PL/SQL: Triggers Cheat Sheet

Below is syntax for creating a trigger:

CREATE [OR REPLACE ] TRIGGER trigger_name  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END; 

Where,

  • CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger with the trigger_name.
  • {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The INSTEAD OF clause is used for creating a trigger on a view.
  • {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.
  • [OF col_name] − This specifies the column name that will be updated.
  • [ON table_name] − This specifies the name of the table associated with the trigger.
  • [REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values for various DML statements, such as INSERT, UPDATE, and DELETE.
  • [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected. Otherwise, the trigger will execute just once when the SQL statement is executed, which is called a table-level trigger.
  • WHEN (condition) − This provides a condition for rows for which the trigger would fire. This clause is valid only for row-level triggers.

Types of PL/SQL Triggers

  • Row-level trigger – An event is triggered for each row updated, inserted, or deleted.
  • Statement level trigger – An event is triggered for each SQL statement executed.

Leave a Reply

Your email address will not be published. Required fields are marked *