PLSQL Triggers:
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.
STATEMENT LEVEL TRIGGER : Trigger which fires only one time when a statement is applied
For Example: Let’s create a table ‘product_check’ which we can use to store messages when triggers are fired.
SQL> CREATE TABLE product_check_8
(Message varchar2(50),
Current_Date DATE);
Let’s create a BEFORE and AFTER statement and row level triggers for the product table.
BEFORE UPDATE, Statement Level: This trigger will insert a record into the table ‘product_check_8’ before a sql update statement is executed, at the statement level.
CREATE or REPLACE TRIGGER Before_Update_Stat_product_8
BEFORE
UPDATE ON product_8
Begin
INSERT INTO product_check_8
Values(‘Before update, statement level’,sysdate);
END;
/
tRIGGER CRATED
SQL> UPDATE PRODUCT_8 SET UNIT_PRICE=UNIT_PRICE+200;
2 rows updated.
SQL> SELECT * FROM product_check_8;
MESSAGE CURRENT_D
Before update, statement level 20-MAY-15
The above trigger fired only one time when the table is updated (though it has updated 2 records);
