PL/SQL- Statement Level Triggers

Triggers which are executed only once for all rows in the transaction are called Statement Level Triggers. These triggers, when fired, make changes in the affected row in one go.

When an INSERT, UPDATE or DELETE statement is executed that affects multiple records in a table, the commands specified in statement level triggers are executed for all those rows. Statement Level triggers are default type of triggers when a CREATE TRIGGER statement is executed without specifying the type of trigger.

In trigger declaration statement ‘for each row’ clause is omitted to make the trigger a Statement level trigger.

CREATE [OR REPLACE] TRIGGER Trigger_name 
 {BEFORE|AFTER} Triggering_event ON table_name 
 [FOLLOWS another_trigger]
 [ENABLE/DISABLE]
 [WHEN condition]
DECLARE
 declaration statements
BEGIN
 executable statements
EXCEPTION
 exception-handling statements
END;

Trigger Predicates

There are three trigger predicates that can be used to determine which event a trigger is responding to. It depend on which DML statement fires it. These predicates are 

• INSERTING- return TRUE if the triggering statement is INSERT statement
• UPDATING- return TRUE if the triggering statement is UPDATE statement
• DELETING- return TRUE if the triggering statement is DELETE statement 

These predicates allow a programmer to control manipulation of data on the basis of the kind of statement is being executed on a table. For example when deleting records from a table you may wish to delete all related records in other tables of the database. 

Consider this scenario– Result table stores overall result of a student. Marks table stores marks in individual subjects scored by the student. You can create an INSERT, UPDATE OR DELETE Trigger in these cases

• Marks are added for another subject for a student in Marks table and you need to update overall result in Result Table
• Marks are modified for an existing subject for a student in Marks table and you need to update overall result in Result Table
• A record is deleted in Marks table for a subject of a student and you need to update overall result in Result Table

An example of Statement Level Triggers

Let there be two tables attendance and att_log. attendance table stores attendance of students filled by an instructor on a specific day. A trigger is created which fires whenever the teacher updates attendance of a student or deletes records from attendance table. This trigger logs details of the action taken by the teacher in table att_log table for record about changes done by teacher in attendance table. 

Table used are

CREATE TABLE attendance (inst_id    number, course_id   varchar2(20), lec_date   date, stu_id     number, atted    varchar2(1));

 CREATE TABLE att_log(user_name varchar2(40), log_ts timestamp, tbl_name varchar2(20), action varchar2(8));

 Code

CREATE OR REPLACE TRIGGER att_trigger 
AFTER UPDATE OR DELETE ON attendance
DECLARE
v_tbl string(20):='attendance';
act string(8);
BEGIN
CASE  
    WHEN UPDATING THEN act:='UPDATE';
    WHEN DELETING THEN act:='DELETE' ;  
END CASE;
insert into att_log values(user,SYSTIMESTAMP ,v_tbl,act);
END;

admin