Database Triggers

Database trigger is a PL/SQL block that is executed on an event in the database.  The event is 
related to a particular data manipulation of a table such as inserting, deleting or updating a 
row of a table.


Triggers may be used for any of the following: 

• To implement complex business rule, which cannot be implemented using integrity constraints. 
• To audit the process.  For example, to keep track of changes made to a table.  
• To automatically perform an action when another concerned action takes place.  For example, updating a table whenever there is an insertion or a row into another  table. 

Triggers are similar to stored procedures, but stored procedures are called explicitly and 
triggers are called implicitly by Oracle when the concerned event occurs. 


Note: Triggers are automatically executed by Oracle and their execution is transparent to 
users.



Types of Triggers 


Depending upon, when a trigger is fired, it may be classified as : 

• Statement-level trigger 
• Row-level trigger 
• Before triggers 
• After triggers

Statement-level Triggers 

A statement trigger is fired only for once for a DML statement irrespective of the number of  rows affected by the statement. For example, if you execute the following UPDATE command STUDENTS table, statement trigger for UPDATE is executed only for once. 

update students   set  bcode=’b3’ where bcode = ‘b2’; 

However, statements triggers cannot be used to access the data that is being inserted, updated or deleted.  In other words, they do not have access to keywords NEW and OLD, which are used to access data

. 

Statement-level triggers are typically used to enforce rules that are not related to data. For example, it is possible to implement a rule that says “no body can modify BATCHES table after 9 P.M”. 

Statement-level trigger is the default type of trigger. 

Row-level Trigger 

A row trigger is fired once for each row that is affected by DML command.  For example, if an UPDATE command updates 100 rows then row-level trigger is fired 100 times whereas a statement-level trigger is fired only for once. 

Row-level trigger are used to check for the validity of the data. They are typically used to implement rules that cannot be implemented by integrity constraints.  

Row-level triggers are implemented by using the option FOR EACH ROW in CREATE TRIGGER 

statement

Before Triggers 

While defining a trigger, you can specify whether the trigger is to be fired before the command (INSERT, DELETE, and UPDATE) is executed or after the command is executed. Before triggers are commonly used to check the validity of the data before the action is performed. For instance, you can use before trigger to prevent deletion of row if deletion should not be allowed in the given case. 

AFTER Triggers 

After triggers are fired after the triggering action is completed. For example, If after trigger is associated with INSERT command then it is fired after the row is inserted into the table. 

Possible Combinations 

The following are the various possible combinations of database triggers. 

• Before Statement 
• Before Row 
• After Statement 
• After Row 

Note: Each of the above triggers can be associated with INSERT, DELETE, and UPDATE 

commands resulting in a total of 12 triggers.