Detail Introduction of Triggers

Triggers provide a procedural technique to specify and maintain integrity constraints.
Triggers even allow users to specify more complex integrity constraints since a trigger essentially is a PL/SQL procedure. Such a procedure is associated with a table and is automatically called by the database system whenever a certain modification (event) occurs on that table.

Modifications on a table may include insert, update, and delete operations.

Structure of Triggers
A trigger definition consists of the following (optional) components:
• trigger name create [or replace] trigger
• trigger time point before | after
• triggering event(s) insert or update [of ] or delete on
• trigger type (optional) for each row
• trigger restriction (only for for each row triggers !) when ()
• trigger body

The clause replace re-creates a previous trigger definition having the same . The name of a trigger can be chosen arbitrarily, but it is a good programming style to use a trigger name that reflects the table and the event(s), e.g., upd ins EMP. A trigger can be invoked before or after the triggering event. The triggering event specifies before (after) which operations on the table the trigger is executed. A single event is an insert, an update, or a delete; events can be combined using the logical connective or. If for an update trigger no columns are specified, the trigger is executed after (before) is updated. If the trigger should only be executed when certain columns are updated, these columns must be
specified after the event update. If a trigger is used to maintain an integrity constraint, the triggering events typically correspond to the operations that can violate the integrity constraint.

it is essential to understand the difference between a row level trigger and a statement level trigger. A row level trigger is defined using the clause for each row. If this clause is not given, the trigger is assumed to be a statement trigger. A row trigger executes once for each row after (before) the event. A statement trigger is executed once after (before) the event, independent of how many rows are affected by the event. For example, a row trigger with the event specification after update is executed once for each row affected by the update. Thus, if the update affects 20 tuples, the trigger is executed 20 times, for each row at a time. In contrast, a statement trigger is only executed once. When combining the different types of triggers, there are twelve possible trigger configurations that can be defined for a table:
trigger time point trigger type event before after statement row

insert X X X X
update X X X X
delete X X X X

Row triggers have some special features that are not provided by statement triggers:
Only with a row trigger it is possible to access the attribute values of a tuple before and after the modification (because the trigger is executed once for each tuple). For an update trigger, the old attribute value can be accessed using  ld. and the new attribute value can be accessed using :new.. For an insert trigger, only :new. can be used, and for a delete trigger only  ld. can be used (because there exists no old, respectively, new value of the tuple). In these cases, :new. refers to the attribute value of of the inserted tuple, and  ld. refers to the attribute value of of the deleted tuple. In a row trigger thus it is possible to specify comparisons between old and new attribute values in the PL/SQL block, e.g., “if ld.SAL < :new.SAL then . . . ”. If for a row trigger the trigger time point before is specified, it is even possible to
modify the new values of the row, e.g., :new.SAL := :new.SAL _ 1.05 or :new.SAL :=  ld.SAL.

Such modifications are not possible with after row triggers. In general, it is advisable to use a after row trigger if the new row is not modified in the PL/SQL block. Oracle then can process these triggers more efficiently. Statement level triggers are in general only used in combination with the trigger time point after.

In a trigger definition the when clause can only be used in combination with a for each row trigger. The clause is used to further restrict when the trigger is executed. For the specification of the condition in the when clause, the same restrictions as for the check clause hold. The only exceptions are that the functions sysdate and user can be used, and that it is possible to refer to the old/new attribute values of the actual row. In the latter case, the colon “:” must not be used, i.e., only old. and new..

The trigger body consists of a PL/SQL block. All SQL and PL/SQL commands except the two statements commit and rollback can be used in a trigger’s PL/SQL block.

if constructs allow to execute certain parts of the PL/SQL block depending on the
triggering event. For this, the three constructs if inserting, if updating[(’’)], and
if deleting exist. They can be used as shown in the following example:

create or replace trigger emp check after insert or delete or update on EMP
for each row begin
if inserting then

end if ;

if updating then

end if ;

if deleting then

end if ;
end;

It is important to understand that the execution of a trigger’s PL/SQL block builds a part of the transaction that contains the triggering event. Thus, for example, an insert statement in a PL/SQL block can cause another trigger to be executed. Multiple triggers and modifications thus can lead to a cascading execution of triggers. Such a sequence of triggers terminates successfully if (1) no exception is raised within a PL/SQL block, and (2) no declaratively specified integrity constraint is violated. If a trigger raises an exception in a PL/SQL block, all modifications up to the beginning of the transaction are rolled back. In the PL/SQL block of a trigger, an exception can be raised using the statement raise application error. This statement causes an implicit rollback. In combination with a row trigger, raise application error can refer to old/new values of modified rows:

raise application error(−20020, ’Salary increase from ’ || to char(:old.SAL) || ’ to ’
to char(:new.SAL) || ’ is too high’); or
raise application error(−20030, ’Employee Id ’ || to char(:new .EMPNO) || ’ does not exist.’);

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.

How to Find 6th Highest Salary from a Table

Select min(salary) from (select salary from emp_table order by salary desc limit 6) from emp_table order by salary

Introduction of Programming Triggers

For a typical programmers, Row triggers are the most critical type of triggers because they include several restrictions. In order to ensure read consistency, Oracle performs an exclusive lock on the table at the beginning of an insert, update, or delete statement. That is, other users cannot access this table until modifications have been successfully completed. In this case, the table currently modified is said to be a mutating table. The only way to access a mutating table in a trigger is to use  ld. and :new. in connection with a row trigger.

Example of an erroneous row trigger:

create trigger check sal EMP after update of SAL on EMP
for each row
declare
sal sum number;
begin
select sum(SAL) into sal sum from EMP;
. . . ;
end;

For example, if an update statement of the form update EMP set SAL = SAL _ 1.1 is executed on the table EMP, the above trigger is executed once for each modified row. While the table is being modified by the update command, it is not possible to access all tuples of the table using the select command, because it is locked. In this case we get the error message

ORA-04091: table EMP is mutating, trigger may not read or modify it
ORA-06512: at line 4
ORA-04088: error during execution of trigger ’CHECK_SAL_EMP’
The only way to access the table, or more precisely, to access the modified tuple, is to use  ld. and :new..

It is recommended to follow the rules below for the definition of integrity maintaining triggers: identify operations and tables that are critical for the integrity constraint
for each such table check

if constraint can be checked at row level then
if checked rows are modified in trigger then
use before row trigger
else use after row trigger
else
use after statement trigger

Triggers are not exclusively used for integrity maintenance. They can also be used for
• Monitoring purposes, such as the monitoring of user accesses and modifications on
certain sensitive tables.
• Logging actions, e.g., on tables: create trigger LOG EMP after insert or update or
delete on EMP
begin
if inserting then
insert into EMP LOG values(user, ’INSERT’, sysdate);
end if ;
if updating then
insert into EMP LOG values(user, ’UPDATE’, sysdate);
end if ;
if deleting then
insert into EMP LOG values(user, ’DELETE’, sysdate);
end if ;
end;

By using a row trigger, even the attribute values of the modified tuples can be stored in the table EMP LOG.

• automatic propagation of modifications. For example, if a manager is transfered to
another department, a trigger can be defined that automatically transfers the
manager’s employees to the new department.

More About Triggers

If a trigger is specified within the SQL*Plus shell, the definition must end with a point “.” In the last line. Issuing the command run causes SQL*Plus to compile this trigger definition. A trigger definition can be loaded from a file using the command @. Note that the last line in the file must consist of a slash “/”.

A trigger definition cannot be changed, it can only be re-created using the or replace clause.

The command drop deletes a trigger.

After a trigger definition has been successfully compiled, the trigger automatically is enabled. The command alter trigger disable is used to deactivate a trigger. All triggers defined on a table can be (de)activated using the command
alter table enable | disable all trigger;

The data dictionary stores information about triggers in the table USER TRIGGERS. The information includes the trigger name, type, table, and the code for the PL/SQL block.