A trigger is a PL/SQL block structure which is fired when DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.
- To design a trigger mechanism, we must meet two requirements:
- Specify when a trigger is to be executed. This is broken up into an event that causes the trigger to be checked and a condition that must be satisfied for trigger execution to proceed.
- Specify the actions to be taken when the trigger executes.
- Once trigger entered into the database the database system takes on the responsibility of executing it whenever the specified event occurs and the corresponding condition is satisfied.
The syntax of Triggers:
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.
Example:
The price of a product changes constantly. It is important to maintain the history of the prices of the products.
We can create a trigger to update the 'product_price_history' table when the price of the product is updated in the 'product' table.
1) Create the 'product' table and 'product_price_history' table
CREATE TABLE product_price_history
(product_id number(5),
product_name varchar2(32),
supplier_name varchar2(32),
unit_price number(7,2) );
CREATE TABLE product
(product_id number(5),
product_name varchar2(32),
supplier_name varchar2(32),
unit_price number(7,2) );
2) Create the price_history_trigger and execute it.
CREATE or REPLACE TRIGGER price_history_trigger BEFORE UPDATE OF unit_price ON product FOR EACH ROW BEGIN INSERT INTO product_price_history VALUES (:old.product_id, :old.product_name, :old.supplier_name, :old.unit_price); END; /
3) Lets update the price of a product.
UPDATE PRODUCT SET unit_price = 800 WHERE product_id = 100
Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.
4)If you ROLLBACK the transaction before committing to the database, the data inserted to the table is also rolled back.
Types of PL/SQL Triggers
There are two types of triggers based on the 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 event will alternate between BEFORE and AFTER row level triggers.
4) Finally the AFTER statement level trigger fires.
For Example: Let's create a table 'product_check' which we can use to store messages when triggers are fired.
CREATE TABLE product(Message varchar2(50), Current_Date number(32));
Let's create a BEFORE and AFTER statement and row level triggers for the product table.
1) BEFORE UPDATE, Statement-Level: This trigger will insert a record into the table 'product_check' before a SQL update statement is executed, at the statement level.
CREATE or REPLACE TRIGGER Before_Update_Stat_product BEFORE UPDATE ON product Begin INSERT INTO product_check Values('Before update, statement level',sysdate); END; /
2) BEFORE UPDATE, Row Level: This trigger will insert a record into the table 'product_check' before each row is updated.
CREATE or REPLACE TRIGGER Before_Upddate_Row_product BEFORE UPDATE ON product FOR EACH ROW BEGIN INSERT INTO product_check Values('Before update row level',sysdate); END; /
3) AFTER UPDATE, Statement-Level: This trigger will insert a record into the table 'product_check' 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_check Values('After update, statement level', sysdate); End; /
4) AFTER UPDATE, Row Level: This trigger will insert a record into the table 'product_check' after each row is updated.
CREATE or REPLACE TRIGGER After_Update_Row_product AFTER insert On product FOR EACH ROW BEGIN INSERT INTO product_check Values('After update, Row level',sysdate); END; /
Now lets execute a update statement on table product.
UPDATE PRODUCT SET unit_price = 800 WHERE product_id in (100,101);
Let's check the data in 'product_check' table to see the order in which the trigger is fired.
SELECT * FROM product_check;
Output:
Message Current_Date
------------------------------------------------------------
Before update, statement level 26-September-2017
Before update, row level 26-September-2017
After update, Row level 26-September-2017
Before update, row level 26-September-2017
After update, Row level 26-September-2017
After update, statement level 26-September-2017
The above result shows 'before update' and 'after update' row level events have occurred twice since two records were updated. But 'before update' and 'after update' statement level events are fired only once per SQL statement.
The above rules apply similarly for INSERT and DELETE statements.
How To know Information about Triggers.
We can use the data dictionary view 'USER_TRIGGERS' to obtain information about any trigger.
The below statement shows the structure of the view 'USER_TRIGGERS'
DESC USER_TRIGGERS;
NAME Type
--------------------------------------------------------
TRIGGER_NAME VARCHAR2(30)
TRIGGER_TYPE VARCHAR2(16)
TRIGGER_EVENT VARCHAR2(75)
TABLE_OWNER VARCHAR2(30)
BASE_OBJECT_TYPE VARCHAR2(16)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
REFERENCING_NAMES VARCHAR2(128)
WHEN_CLAUSE VARCHAR2(4000)
STATUS VARCHAR2(8)
DESCRIPTION VARCHAR2(4000)
ACTION_TYPE VARCHAR2(11)
TRIGGER_BODY LONG
This view stores information about header and body of the trigger.
SELECT * FROM user_triggers WHERE trigger_name = 'Before_Update_Stat_product';
The above sql query provides the header and body of the trigger 'Before_Update_Stat_product'.
You can drop a trigger using the following command.
DROP TRIGGER trigger_name;
CYCLIC CASCADING in a TRIGGER
This is an undesirable situation where more than one trigger enter into an infinite loop. while creating a trigger we should ensure the such a situation does not exist.
The below example shows how triggers can enter into cyclic cascading.
Let's consider we have two tables 'abc' and 'xyz'. Two triggers are created.
1) The INSERT Trigger, trigger A on table 'abc' issues an UPDATE on table 'xyz'.
2) The UPDATE Trigger, trigger B on table 'xyz' issues an INSERT on table 'abc'.
In such a situation, when there is a row inserted in table 'abc', trigger A fires and will update table 'xyz'.
When the table 'xyz' is updated, trigger B fires and will insert a row in table 'abc'.
This cyclic situation continues and will enter into an infinite loop, which will crash the database.
