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.