Creating Triggers

The syntax for creating a trigger is:

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 would be executed. The INSTEAD OF clause is used for creating trigger on a view.
• {INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML operation.
• [OF col_name]: This specifies the column name that would 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, like INSERT, UPDATE, and DELETE.
• [FOR EACH ROW]: This specifies a row level trigger, i.e., the trigger would 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.