-
Copyrights 2010 - Online Training Oracle Applications. All Rights Reserved.
|
PL/ SQL:
Trigger is a PL/SQL block which is executed automatically Triggering events: Insert, Update, Delete Trigger timings: Before, after, instead of Syntax: Create or replace trigger <trg_name> ex: create or replace trigger trg1 Trigger Created. Now, when we peroform the event, trigger is executed,. ex: insert into dept values (52,'HR','HYDERABAD'); Thank You 1 row created. We get the message, 'Thank You'. We can create triggers on multiple events. ex: create or replace trigger trg1 Trigger created. Now, for all the three events , triggger is fired. ex: Update dept set loc='DELHI' Thank You delete from dept where deptno=50; In the above program, we get the same message for all the events. We can also have different messages to be displayed, basing on the events. Ex: create or replace trigger trg1 if inserting then end; Trigger created. In the above program, inserting and updating are the key words which are used to identify the events. Triggers can be classified into two types, basing on the no of times it is executed. 1) Statement level triggers 1) Statement level triggers are executed only once, irrespective of no of rows effected by the event. 2) Row level triggers are executed for every row effected by the event. To create a row level trigger, we need to use ex: create or replace trigger trg1 Trigger created. update emp set sal=2000 Thank you for updating 3 rows updated. As, the update command is effecting 3 rows, trigger is executed 3 times. Triggers are used to enforce business rules by ex: Create a trigger which restrict insert operation create or replace trigger trg1 if :new.sal >5000 then end if; end; Trigger Created. Event: insert into emp( empno, ename,sal, deptno ) ERROR:
Ex: create or replace trigger trg1 if :OLD.JOB='PRESIDENT' then raise_application_error(-20151, end if; end; Trigger created. Event: delete from emp where ename='KING'; Error: Instead of triggers: Instead of triggers are helpful to perform DML operations on complex view. Example of complex view: create or replace view emp_dept_v View created. Generally, we cannot insert row into complex view. ex: create or replace trigger trg1 insert into dept values (:NEW.deptno,:NEW.dname, :NEW.loc); insert into emp ( empno, ename,sal, deptno) values end; Trigger Created. Event: 1 Row created. To see the list of triggers: To drop a trigger: Ex: Trigger Droped.
|