Developer Geeks Home

Creating and using Triggers in Oracle PL SQL

RSS
 

  • Posted one year ago
  • Category: Oracle, SQL Server,
  • Audiences: Architect, Database Developer, Developer, System Analyst,

This article explains different types of triggers in Oracle PL SQL and how to create and use these triggers, with sample code.

Types of Triggers in PL SQL

There are two types of triggers based on the level it is triggered:

1) Row level trigger - It is triggered for each row updated, inserted or deleted.
2) Statement level trigger - It is triggered for each sql statement executed.

Trigger Execution Hierarchy in PL SQL

The following hierarchy is followed when a trigger is fired:

1) BEFORE statement trigger fires first.
2) Next BEFORE row level trigger fires, once for each row affected.
3) Then AFTER row level trigger fires once for each affected row.
4) Finally the AFTER statement level trigger fires.

Let's create a BEFORE and AFTER statement and row level triggers for the Employee table.

BEFORE UPDATE, Statement Level

This trigger will insert a record into the table 'Employee_History' before a sql update statement is executed, at the statement level. 

CREATE or REPLACE TRIGGER Before_Update_Stat_Employee 
BEFORE 
UPDATE ON Employee 
Begin 
INSERT INTO Employee_History 
Values('Before update, statement level',sysdate); 
END;

BEFORE UPDATE, Row Level

This trigger will insert a record into the table 'Employee_History' before each row is updated.

CREATE or REPLACE TRIGGER Before_Upddate_Row_Employee 
 BEFORE 
 UPDATE ON Employee 
 FOR EACH ROW 
 BEGIN 
 INSERT INTO Employee_History 
 Values('Before update row level',sysdate); 
 END;

AFTER UPDATE, Statement Level

This trigger will insert a record into the table 'Employee_History' after a sql update statement is executed, at the statement level.

CREATE or REPLACE TRIGGER After_Update_Stat_Employee 
 AFTER 
 UPDATE ON Employee 
 BEGIN 
 INSERT INTO Employee_History 
 Values('After update, statement level', sysdate); 
 END;

AFTER UPDATE, Row Level

This trigger will insert a record into the table 'Employee_History' after each row is updated.

CREATE or REPLACE TRIGGER After_Update_Row_Employee 
 AFTER  
 insert On Employee 
 FOR EACH ROW 
 BEGIN 
 INSERT INTO Employee_History 
 Values('After update, Row level',sysdate); 
 END;

Similarly, these triggers could be used for INSERT and DELETE statements.

comments powered by Developer Geeks

My Recent Articles [130]