Example of Triggers

Suppose we have to maintain the following integrity constraint: “The salary of an employee different from the president cannot be decreased and must also not be increased more than 10%. Furthermore, depending on the job title, each salary must lie within a certain salary range.

We assume a table SALGRADE that stores the minimum (MINSAL) and maximum (MAXSAL) salary for each job title (JOB).

Since the above condition can be checked for each employee individually,
we define the following row trigger:
trig1.sql

create or replace trigger check salary EMP after insert or update of SAL, JOB on EMP
for each row when (new.JOB != ’PRESIDENT’) – – trigger restriction
declare minsal, maxsal SALGRADE.MAXSAL%TYPE;
begin

– – retrieve minimum and maximum salary for JOB
select MINSAL, MAXSAL into minsal, maxsal from SALGRADE where JOB = :new.JOB;

– – If the new salary has been decreased or does not lie within the salary range,
– – raise an exception
if (:new.SAL maxsal) then raise application error(-20225, ’Salary range exceeded’);
elsif (:new.SAL 1.1 _  ld.SAL) then
raise application error(-20235, ’More than 10% salary increase’);
end if ;
end;

We use an after trigger because the inserted or updated row is not changed within the PL/SQL block (e.g., in case of a constraint violation, it would be possible to restore the old attribute values). Note that also modifications on the table SALGRADE can cause a constraint violation. In order to maintain the complete condition we define the following trigger on the table SALGRADE. In case of a violation by an update modification, however, we do not raise an exception, but restore the old attribute values.

trig2.sql
create or replace trigger check salary SALGRADE before update or delete on SALGRADE for each row when (new.MINSAL > old.MINSAL
or new.MAXSAL ALLSAL then
raise application error(-20325, ’Total of salaries in the department ’ || to char(DNO) || ’ exceeds budget’);
end if ;
end loop;
close DEPT CUR;
end;

In this case we use a statement trigger on the relation EMP because we have to apply an aggregate function on the salary of all employees that work in a particular department. For the relation DEPT, we also have to define a trigger which, however, can be formulated as a row trigger.