Thursday, December 2, 2010

Triggle in Oracle 10g

Trigger in Pl-sql/oracle/Oracle 10g forms and reports

1.    it is a db object which is a collection of pl/sql codes and it automatically get executed whenever any dml event happens provided trigger has been written for that DML event.
2.    Trigger has three section i.e. events, restrictions and when condition (optional.
3.    Pl sql trigger is divided into two parts. 1. Row level 2. Statement level.

Row Level Statement Trigger.
1.    Row level trigger is executed for each and every row affected by DML stmt.
2.    we need to use for each row for row level trigger.
3.    used in those cases where we need to depend on the values of every record of any column of any table.
4.    eg. Any name begin inserted must be in letter capital.

Statement Level Trigger.

1.    Statement level trigger is executed only once irrespective of No. of records affected by the dml statement.
2.    Every trigger is by default STMT level trigger.
3.    Used for restrictions like No Deletion allowed on Sunday on emp table.



ON FOR EACH ROW (OPTIONAL) REFRENCING NEW AS  OLD AS WHEN () (OPTIONAL) Note. 1.    to follow any value of any col. Of any record of any table within the execution section of a trigger always used :old.and :new.2.    Theses two can be followed only with in a row level trigger. :new. Refers to new values or that col for that row (Applicable for updated and insert) :old.Refers to old values for that col for that record (Applicable for both delete and update) Example:- Create or replace  Trigger tr_ex Before delete On  insert or update On emp For each row Declare  V_msg varchar2(200) Begin If inserting then  V_msg := ‘inserting….’ Elsif deleting then  V_msg := ‘deleting………….’; Elsif updating then V_msg := ‘deleting…’; End if; Dbms_output.put_line(V_msg); End; Q.    Create a trigger which will automatic make the first letter of  every name inserted | updated into capital letter irrespective of any case enter by user. Create or replace trigger Tr_name Before update or insert Of ename On emp For each row Begin Dbms_output.put_line(‘before change name = ‘ ||.new.ename); End; / Example of Statement level trigger. CREATE OR REPLACE TRIGGER TR_DELETE BEFORE DELTE ON EMP BEGIN IF TRIM(TO_CHAR(SYSDATE,’DAY’)) = ‘SUNDAY’ THEN RAISE _APPLICATION_ERROR  ( -20001,’NO DELTETION ALLOW ON SUNDAY); END IF; END; INSTEAD OF TRIGGER. This is used to perform DML operation on base table through join view. Create or replace trigger tr_instead  Instead of  Insert or update or delete On v_join Referencing new as N old as O For each row Declare V_count number; Begin  If inserting then Select count(*) into v_count from dept Where deptno = :n.deptno; If v_count = 0 then  Insert into dept values(:n.deptno,:n.dname,:n.loc); End if; Insert into emp (empno,ename,sal) values (:n.empno,:n.ename,:n.sal); Elsif updating then Update emp Set sal = :n.sal where empno = :o.empno; Update dept Set dname = :n.dname where deptno = :o.deptno; Elsif deleting then  Delete from emp where empno = :o.empno; Delete from dept where deptno = :o.deptno; End if; End; / 

No comments:

Post a Comment