PL/SQL - Trigger
TRIGGERS:
->explicit calling -> called by the programmer
->implicit calling -> called by Oracle Engine -> auto calling
->Stored Procedures & Functions get explicity called. (by the programmer)
->Oracle Engine allows us to create / define procedures that get implicitly called (auto called) when a DML statement (INSERT, UPDATE, DELETE) is issued against a specific table from the SQL+ or an application.
->These spl. types of procedures are called TRIGGERS.
->Trigger is a spl. type of a Procedure that gets implicitly called when we run a DML statement on a specific table.
Triggers v/s Procedures:
->triggers do not accept arguments whereas procedures may/may not accept arguments.
->triggers get implicitly executed/called whereas procedures get explicitly executed / called.
Types of Triggers:
1. Row Trigger:
This type of a trigger gets fired each time a row in a table gets affected by the trigger statement.
2. Statement Trigger:
This type of a trigger is fired on behalf of the triggerring statement independent of the no. of rows affected. (even if no rows are affected, this will be fired).
Before Triggers:
executes the trigger action before the execution of the triggerring statement.
After Triggers:
executes the trigger action after the execution of the triggerring statement.
Before Row Trigger
After Row Trigger
Before Statement Trigger
After Statement Trigger
------------------------------------------------------------------------
Creating a Trigger:
CREATE OR REPLACE TRIGGER triggername
{BEFORE, AFTER}
{INSERT OR UPDATE OR DELETE}
ON tablename
FOR EACH ROW [WHEN condition]
DECLARE
variable declaration
BEGIN
pl/SQL code block
EXCEPTION
exception handling
END;
-----------------------------------------------------
Inside a trigger, we can access the old column values (while doing update & delete operations) using
:OLD.columnname
and we can access the new column values (while doing insert operation) using
:NEW.columnname
to check what type of DML query fired the trigger use:
->inserting
->updating
->deleting
----------------------------------------------------
Create a trigger for recording the update & delete operations done on the emptab. the recording must occur inside the emplogtab;
CREATE OR REPLACE TRIGGER TRIG_EMP_TRANS_LOG
AFTER UPDATE OR DELETE
ON emptab
FOR EACH ROW
DECLARE
trans varchar2(10);
BEGIN
/*check the type of DML query*/
IF updating THEN
/*set the trans var*/
trans:='UPDATE';
ELSIF deleting THEN
/*set the trans var*/
trans:='DELETE';
END IF;
/*insert the old values into the emplogtab*/
INSERT INTO emplogtab
(empid,empname,dept,mgrid,transaction,dateoftrans)
VALUES(:OLD.empid, :OLD.empname, :OLD.dept, :OLD.mgrid, trans, SYSDATE);
END;
-------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER TR_EMPLG_2
AFTER INSERT OR UPDATE OR DELETE
ON emptab2
FOR EACH ROW
DECLARE
trns varchar2(12);
BEGIN
IF inserting THEN
trns:='INSERT';
ELSIF updating THEN
trns:='UPDATE';
ELSIF deleting THEN
trns:='DELETE';
END IF;
IF trns='INSERT' THEN
INSERT INTO emplog
(empid,empname,dept,mgrid,trans,transdt)
VALUES(:NEW.empid, :NEW.empname, :NEW.dept, :NEW.mgrid, trns, SYSDATE);
ELSE
INSERT INTO emplog
(empid,empname,dept,mgrid,trans,transdt)
VALUES(:OLD.empid, :OLD.empname, :OLD.dept, :OLD.mgrid, trns, SYSDATE);
END IF;
END;
/
CREATE OR REPLACE TRIGGER TR_PHONETAB
AFTER INSERT OR UPDATE OR DELETE
ON phonetab1
FOR EACH ROW
DECLARE
ops varchar2(10);
BEGIN
IF inserting THEN
ops:='INSERT';
ELSIF updating THEN
ops:='UPDATE';
ELSIF deleting THEN
ops:='DELETE';
END IF;
IF ops='INSERT' THEN
INSERT INTO phonelogtab
(operation,id,brand,model,price)
VALUES(ops, :NEW.id, :NEW.brand, :NEW.model,:NEW.price);
ELSE
INSERT INTO phonelogtab
(operation,id,brand,model,price)
VALUES(ops, :OLD.id, :OLD.brand, :OLD.model,:OLD.price);
END IF;
END;
/
--------------------------------------------------------------------------
Practical:
SQL*Plus: Release 11.2.0.2.0 Production on Sun Mar 3 12:48:52 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: system
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
SQL> SELECT * FROM emptab;
EMPID EMPNAME DEPT MGRID
---------- ---------- ---------- ----------
101 julie cse
102 toffee cse 101
103 coco cse 101
104 ramni HR
105 brownie HR 104
106 candy HR 104
6 rows selected.
SQL> CREATE TABLE emplogtab
2 (
3 ;
*
ERROR at line 3:
ORA-00904: : invalid identifier
SQL> DESC emptab;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NOT NULL NUMBER(4)
EMPNAME VARCHAR2(10)
DEPT VARCHAR2(10)
MGRID NUMBER(4)
SQL> CREATE TABLE emplogtab
2 (
3 empid number(4),
4 empname varchar2(10),
5 dept varchar2(10),
6 mgrid number(4),
7 transaction varchar(10)
8 );
Table created.
SQL> ALTER TABLE emplogtab
2 ADD dateoftrans date;
Table altered.
SQL> DESC emplogtab;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NUMBER(4)
EMPNAME VARCHAR2(10)
DEPT VARCHAR2(10)
MGRID NUMBER(4)
TRANSACTION VARCHAR2(10)
DATEOFTRANS DATE
SQL> CREATE OR REPLACE TRIGGER TRIG_EMP_TRANS_LOG
2 AFTER UPDATE OR DELETE
3 ON emptab
4 FOR EACH ROW
5 DECLARE
6 trans varchar2(10);
7 BEGIN
8 /*check the type of DML query*/
9 IF updating THEN
10 /*set the trans var*/
11 trans:='UPDATE';
12 ELSIF deleting THEN
13 /*set the trans var*/
14 trans:='DELETE';
15 END IF;
16 /*insert the old values into the emplogtab*/
17 INSERT INTO emplogtab
18 (empid,empname,dept,mgrid,transaction,dateoftrans)
19 VALUES(:OLD.empid, :OLD.empname, :OLD.dept, :OLD.mgrid, trans, SYSDATE);
20 END;
21 /
Trigger created.
SQL> SELECT * FROM emplogtab;
no rows selected
SQL> SELECT * FROM emptab;
EMPID EMPNAME DEPT MGRID
---------- ---------- ---------- ----------
101 julie cse
102 toffee cse 101
103 coco cse 101
104 ramni HR
105 brownie HR 104
106 candy HR 104
6 rows selected.
SQL> UPDATE emptab SET empname='rammi' WHERE empid=104;
1 row updated.
SQL> SELECT * FROM emptab;
EMPID EMPNAME DEPT MGRID
---------- ---------- ---------- ----------
101 julie cse
102 toffee cse 101
103 coco cse 101
104 rammi HR
105 brownie HR 104
106 candy HR 104
6 rows selected.
SQL> SELECT * FROM emplogtab;
EMPID EMPNAME DEPT MGRID TRANSACTIO DATEOFTRA
---------- ---------- ---------- ---------- ---------- ---------
104 ramni HR UPDATE 03-MAR-13
SQL> DELETE FROM emptab WHERE empid=106;
1 row deleted.
SQL> SELECT * FROM emptab;
EMPID EMPNAME DEPT MGRID
---------- ---------- ---------- ----------
101 julie cse
102 toffee cse 101
103 coco cse 101
104 rammi HR
105 brownie HR 104
SQL> SELECT * FROM emplogtab;
EMPID EMPNAME DEPT MGRID TRANSACTIO DATEOFTRA
---------- ---------- ---------- ---------- ---------- ---------
104 ramni HR UPDATE 03-MAR-13
106 candy HR 104 DELETE 03-MAR-13
SQL>
------------------------------------------------------------------------------
Create a trigger for recording the insert, update & delete operations done on the emptab. the recording must occur inside the emplogtab;
CREATE OR REPLACE TRIGGER TRIG_EMP_TRANS_LOG
AFTER INSERT OR UPDATE OR DELETE
ON emptab
FOR EACH ROW
DECLARE
trans varchar2(10);
BEGIN
/*check the type of DML query*/
IF inserting THEN
/*set the trans var*/
trans:='INSERT';
ELSIF updating THEN
/*set the trans var*/
trans:='UPDATE';
ELSIF deleting THEN
/*set the trans var*/
trans:='DELETE';
END IF;
IF trans='INSERT' THEN
/*insert the newly inserted values into the emplogtab*/
INSERT INTO emplogtab
(empid,empname,dept,mgrid,transaction,dateoftrans)
VALUES(:NEW.empid, :NEW.empname, :NEW.dept, :NEW.mgrid, trans, SYSDATE);
ELSE
/*insert the old values into the emplogtab*/
INSERT INTO emplogtab
(empid,empname,dept,mgrid,transaction,dateoftrans)
VALUES(:OLD.empid, :OLD.empname, :OLD.dept, :OLD.mgrid, trans, SYSDATE);
END IF;
END;
----------------------------------------------------------------------------------------
Practicals:
SQL> CREATE OR REPLACE TRIGGER TRIG_EMP_TRANS_LOG
2 AFTER INSERT OR UPDATE OR DELETE
3 ON emptab
4 FOR EACH ROW
5 DECLARE
6 trans varchar2(10);
7 BEGIN
8 /*check the type of DML query*/
9 IF inserting THEN
10 /*set the trans var*/
11 trans:='INSERT';
12 ELSIF updating THEN
13 /*set the trans var*/
14 trans:='UPDATE';
15 ELSIF deleting THEN
16 /*set the trans var*/
17 trans:='DELETE';
18 END IF;
19
20 IF trans='INSERT' THEN
21 /*insert the newly inserted values into the emplogtab*/
22 INSERT INTO emplogtab
23 (empid,empname,dept,mgrid,transaction,dateoftrans)
24 VALUES(:NEW.empid, :NEW.empname, :NEW.dept, :NEW.mgrid, trans, S
YSDATE);
25 ELSE
26 /*insert the old values into the emplogtab*/
27 INSERT INTO emplogtab
28 (empid,empname,dept,mgrid,transaction,dateoftrans)
29 VALUES(:OLD.empid, :OLD.empname, :OLD.dept, :OLD.mgrid, trans, S
YSDATE);
30 END IF;
31 END;
32 /
Trigger created.
SQL> SELECT * FROM emplogtab;
EMPID EMPNAME DEPT MGRID TRANSACTIO DATEOFTRA
---------- ---------- ---------- ---------- ---------- ---------
104 ramni HR UPDATE 03-MAR-13
106 candy HR 104 DELETE 03-MAR-13
SQL> SELECT * FROM emptab;
EMPID EMPNAME DEPT MGRID
---------- ---------- ---------- ----------
101 julie cse
102 toffee cse 101
103 coco cse 101
104 rammi HR
105 brownie HR 104
SQL> INSERT INTO emptab
2 VALUES(106, 'candy', 'HR',104);
1 row created.
SQL> UPDATE emptab
2 SET mgrid=105
3 WHERE empid=106;
1 row updated.
SQL> SELECT * FROM emptab;
EMPID EMPNAME DEPT MGRID
---------- ---------- ---------- ----------
101 julie cse
102 toffee cse 101
103 coco cse 101
104 rammi HR
105 brownie HR 104
106 candy HR 105
6 rows selected.
SQL> SELECT * FROM emplogtab;
EMPID EMPNAME DEPT MGRID TRANSACTIO DATEOFTRA
---------- ---------- ---------- ---------- ---------- ---------
104 ramni HR UPDATE 03-MAR-13
106 candy HR 104 DELETE 03-MAR-13
106 candy HR 104 INSERT 03-MAR-13
106 candy HR 104 UPDATE 03-MAR-13
SQL>
----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
CREATE A TRIGGER for insertion into the studenttab that first creates a valid roll no by combining 'PU' with rollseq.
CREATE OR REPLACE TRIGGER TRIG_GEN_ROLL_SEQ
BEFORE INSERT
ON studenttab
FOR EACH ROW
DECLARE
roll varchar2(5);
BEGIN
/*create the roll*/
roll:='PU'||TO_CHAR(rollseq.NEXTVAL);
/*update the roll*/
:NEW.ROLL_NO:=roll;
END;
----------------------------------------------------------------------------------------
Practical:
-------------------------------------------------------------------------------
SQL> create table studenttab (roll_no varchar2(5),name varchar2(10));
Table created.
SQL> DESC studenttab;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLL_NO VARCHAR2(5)
NAME VARCHAR2(10)
SQL> CREATE SEQUENCE rollseq
2 INCREMENT BY 1 START WITH 100;
Sequence created.
SQL> CREATE OR REPLACE TRIGGER TRIG_GEN_ROLL_SEQ
2 BEFORE INSERT
3 ON studenttab
4 FOR EACH ROW
5 DECLARE
6 roll varchar2(5);
7 BEGIN
8 /*create the roll*/
9 roll:='PU'||TO_CHAR(rollseq.NEXTVAL);
10 /*update the roll*/
11 :NEW.ROLL_NO:=roll;
12 END;
13 /
Trigger created.
SQL> INSERT INTO studenttab
2 (name)
3 VALUES('Amit');
1 row created.
SQL> SELECT * FROM studenttab;
ROLL_ NAME
----- ----------
PU100 Amit
SQL> INSERT INTO studenttab
2 (name)
3 VALUES('Sumit');
1 row created.
SQL> INSERT INTO studenttab
2 (name)
3 VALUES('Kamit');
1 row created.
SQL> SELECT * FROM studenttab;
ROLL_ NAME
----- ----------
PU100 Amit
PU101 Sumit
PU102 Kamit
SQL>
-----------------------------------------------------------------------------------------------------------------------------------
0 Comments