3 TYPES
1.DATABASE TRIGGERS (TABLE) CLASSIFIED INTO STATEMENT LEVEL AND ROW LEVEL
2.SYSTEM TRIGGERS(SYSTEM)
3.INSTEAD OF TRIGGERS(VIEW)
DATABASE TRIGGERS
• commit,rollback and savepoint are not allowed within trigger
• only update,insert,delete can be used
STATEMENT LEVEL TRIGGERS
SQL> SELECT *FROM STUDENT;
ROLLNO NAME STD
---------- ---------- ----------
12345 KAR 11
2345 joe 10
12346 tarun 9
7890 MARIE 12
1357 VIMAL 10
1 CREATE OR REPLACE TRIGGER INTIMATE
2 AFTER INSERT OR UPDATE OR DELETE
3 ON STUDENT
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE('DML INSERT OR UPDATE OR DELETE EXECUTED');
6 END;
7 /
TRIGGER CREATED
SQL> UPDATE STUDENT SET NAME='KAR' WHERE STD=10;
DML INSERT OR UPDATE OR DELETE EXECUTED
2 rows updated.
ROW LEVEL TRIGGERS
CREATE OR REPLACE TRIGGER INTIMATE
AFTER INSERT OR UPDATE OR DELETE
ON STUDENT
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('DML INSERT OR UPDATE OR DELETE EXECUTED');
END;
SQL> UPDATE STUDENT SET NAME='KAR' WHERE STD=10;
DML INSERT OR UPDATE OR DELETE EXECUTED
DML INSERT OR UPDATE OR DELETE EXECUTED
2 rows updated.
USING OLD and NEW Keyword
1 CREATE OR REPLACE TRIGGER NAMECHANGE
2 AFTER UPDATE ON STUDENT
3 FOR EACH ROW
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE(:OLD.NAME || ' CHANGED TO '|| :NEW.NAME);
6* END;
SQL> /
Trigger created.
SQL> SELECT *FROM STUDENT;
ROLLNO NAME STD
---------- ---------- ----------
12346 tarun 9
7890 MARIE 12
1357 KARTHIK 10
SQL> UPDATE STUDENT SET NAME='TARUN' WHERE NAME='tarun';
tarun CHANGED TO TARUN
1 row updated.
Note : OLD and NEW can be used only with row level triggers
old value new value
update before after
insert null after
delete before null
Using with when clause and referencing
CREATE OR REPLACE TRIGGER NOTIFY
AFTER INSERT ON STUDENT
REFERENCING OLD AS PREV NEW AS CURR
FOR EACH ROW
WHEN(CURR.NAME='KARTHIK')
BEGIN
DBMS_OUTPUT.PUT_LINE('Student KARTHIK already exists but however it is 2nd time inserted');
END;
SQL> insert into student values(3934,'KARTHIK',11);
Student KARTHIK already exists but however it is 2nd time inserted
1 row created.
To drop a trigger
drop trigger triggername;
MANAGING TRIGGERS
SQL> ALTER TRIGGER NOTIFY COMPILE;
Trigger altered.
SQL> ALTER TRIGGER NOTIFY DISABLE;
Trigger altered.
insert into student values(5034,'KARTHIK',11);
1 row created.
SQL> ALTER TRIGGER NOTIFY ENABLE;
Trigger altered.
SQL> insert into student values(5035,'KARTHIK',11);
Student KARTHIK already exists but however it is 2nd time inserted
1 row created.
SQL> ALTER TABLE STUDENT DISABLE ALL TRIGGERS;
Table altered.
SQL> ALTER TABLE STUDENT ENABLE ALL TRIGGERS;
Table altered.
MUTATION ERROR occurs in Row level Triggers only. There are two possible ways to find it
1. create trigger before update of sal on emp .. blah blah begin ..modify emp then mutation results
2. A special case: create trigger before update on sal ... blah blah foreign key ref...
SYSTEM TRIGGERS
• CREATE,ALTER,DROP,LOGON,LOGOFF,STARTUP,SHUTDOWN are used i.e DDL and system events are considered. Refer book for eg.
INSTEAD OF
• for views to restrict insert,delete or update
SQL> CREATE OR REPLACE TRIGGER TRG1
2 INSTEAD OF INSERT ON EMPLOC
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('INSERTING DATA ON VIEW EMPLOC RESTRICTED');
5 END;
6 /
Trigger created.
Eg.
insert into emploc('jose','japan');
INSERTING DATA ON VIEW EMPLOC RESTRICTED
1 row created.
though it says '1 row created' in the view no new value get inserted.
