I'm new in triggers and I'm trying to create a trigger that will be activated before inserting or updating a row in a table. The trigger works when the manager_id (id_cap) has 2 or more employees (id_empleat) or when the sum of the salary of the department is 1000 or more.
This is the table:
Create table empleats (
id_empleat number,
empleat varchar2(200),
id_cap number,
salary number(9,2),
dpto varchar2(20),
constraint empleat_pk primary key (id_empleat),
constraint empleat_con foreign key (id_cap) references empleats(id_empleat) on delete cascade);
The trigger of the manager:
drop trigger supervisor_tr;
CREATE OR REPLACE TRIGGER supervisor_tr
BEFORE INSERT or UPDATE of id_cap on empleats
for each row
DECLARE
numero number;
pragma autonomous_transaction;
BEGIN
if inserting then
select count(*) into numero from empleats
where id_cap = :new.id_cap;
if numero >= 2 then
rollback;
RAISE_APPLICATION_ERROR(-20090,'ERROR: El jefe especificado tiene ya 2 empleados a su cargo, introduccion rechazada');
end if;
elsif updating('id_cap') then
select count(*) into numero from empleats
where id_cap = :new.id_cap;
if numero >= 2 then
rollback;
RAISE_APPLICATION_ERROR(-20091,'ERROR: El jefe especificado tiene ya 2 empleados a su cargo, actualizacion rechazada');
end if;
end if;
END;
/
The trigger of the salary:
create or replace trigger presupuesto_1000_tr
before insert or update of salary on empleats
for each row
declare
presupuesto number;
pragma autonomous_transaction;
begin
if updating or inserting then
select sum(salary) into presupuesto from empleats where dpto = :new.dpto;
presupuesto := presupuesto + :new.salary;
if presupuesto >= 1000 then
rollback;
raise_application_error(-20013,'ERROR: EL DEPARTAMENTO SUPERA EL PRESUPUESTO ADMITIDO (1000)');
end if;
end if;
end;
/
and here some of the insert examples I've tried:
insert into empleats values (1,'James',null,500,'HR');
insert into empleats values (2,'Alex',1,2000,'HR');
insert into empleats values (3,'Robin',1,5000,'IT');
insert into empleats values (4,'Pat',1,500,'Accounting');
The problem is that no matter what I insert or update, the triggers don't work. I've tried without the pragma autonomus_transation and only works with inserts (with updates I get the "mutating table error").
