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.
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS :old NEW AS :new]
[FOR EACH ROW]
WHEN (condition)
BEGIN
— sql statements
END;
ROW LEVEL TRIGGER
Example: Create 2 tables one is for product informatiopn and another one is maintain history
SQL> CREATE TABLE product_8
2 (product_id number(5),
3 product_name varchar2(32),
4 supplier_name varchar2(32),
5 unit_price number(7,2) );
Table created.
SQL> CREATE TABLE product_price_history_8
2 (product_id number(5),
3 product_name varchar2(32),
4 supplier_name varchar2(32),
5 unit_price number(7,2) );
Table created.
Insert few records in product_8 table
SQL> INSERT INTO PRODUCT_8 VALUES (101,’LP001′,’DELL’,15000);
SQL> INSERT INTO PRODUCT_8 VALUES (102,’LP002′,’PHILIPS’,10000);
SQL> SELECT * FROM PRODUCT_8;
PRODUCT_ID PRODUCT_NAME SUPPLIER_NAME UNIT_PRICE
101 LP001 DELL 15000
102 LP002 PHILIPS 10000SQL> commit;
Now create the trigger to maintain the price history when the unit_price is changed in product_8 table
SQL> CREATE or REPLACE TRIGGER price_history_trigger_8
2 BEFORE UPDATE OF unit_price
3 ON product_8
4 FOR EACH ROW
5 BEGIN
6 INSERT INTO product_price_history_8
7 VALUES
8 (:old.product_id,
9 :old.product_name,
10 :old.supplier_name,
11 :old.unit_price);
12 END;
13 /
Created trigger
SQL> UPDATE PRODUCT_8 SET UNIT_PRICE = UNIT_PRICE – 5000;
2 rows updated
SQL> selet * from product_8
RODUCT_ID PRODUCT_NAME SUPPLIER_NAME UNIT_PRICE
101 LP001 DELL 10000
102 LP002 PHILIPS 5000SQL> SELECT * FROM product_price_history_8;
PRODUCT_ID PRODUCT_NAME SUPPLIER_NAME UNIT_PRICE
101 LP001 DELL 15000
102 LP002 PHILIPS 10000
Note : When records updated, the trigger is fired 2 times because of 2 records updated.
