Triggers and Cursors

Trigger

A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

Types of PL/SQL Triggers

There are two types of triggers based on which level it is triggered.
1) Row level trigger - An event is triggered for each row updated, inserted or deleted. 
2) Statement level trigger - An event is triggered for each sql statement executed. 

PL/SQL Trigger Execution Hierarchy

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. This events will alternates between BEFORE and AFTER row level triggers.
4) Finally the AFTER statement level trigger fires.

Syntax of 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)  

 BEGIN 

   --- sql statements  

 END; 

Example

Row Level: This trigger will insert a record into the table 'product' after each row is updated.


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


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

CREATE or REPLACE TRIGGER After_Update_Stat_product 
 AFTER 
 UPDATE ON product 
 BEGIN 
 INSERT INTO product 
 Values('After update, statement level', sysdate); 
 End; 
 / 


 Cursors



A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called theactive set.

There are two types of cursors in PL/SQL:

Implicit cursors:

These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed. 

Explicit cursors:

They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.
Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.