Triggers in Oracle

 Database Triggers:
 - A set of pl/sql statements stored permenantly in    database and "automatically" activated when ever    an event raising statement ( DML ) is executed.
 - They are stored in "User_Triggers" system table.
 - They r used to impose Business rules / user        defined restrictions on Table.
 - They r also activated when Tables are     manipulated by other users or by other application   s/w tools.
 - They provide high security on tables.

 Trigger Parts:  4 Parts
 1. Triggering Event : Indicates when to activate the       trigger .    

Before  -- insert / update / delete
After   --  insert / update / delete 

 2. Trigger Types: 2 Types
 i) Row Triggers : Activates the trigger for every      row manipulated by DML statement.
 ii) Statement Triggers: Activates the trigger for        only once  for 1 DML statement ( Default Type )

 3. Trigger Restriction:
  Used to stop the activation of trigger based on     condition.  If condition is True trigger is active.

 4. Trigger Body: A set of pl/sql statements

 Syntax:
 Create or replace Trigger < Trigger name >
 before/after insert or update or delete 
 [ of <columns> ] on < Table name >
 [ for each row
 when (< condition >) * if condition is True trigger                                         is  executed
 declare                                  
   < variable declaration >; ]
 begin
   < exec stmts >;
 [ exception
   <exec stmts >; ]
 end;---------------------------------------------------------------

* Trigger converts student name to upper case        automatically. create or replace Trigger up_con
 before insert on stu_info
 for each row
 begin
  :new.sname = upper(:new.sname);
 end;

 insert into stu_info    values(101,'ram','ORACLE',2500);
 >select * from stu_info;
   101  RAM  ORACLE  2500
---------------------------------------------------------------  

New & Old (Pseudo Columns):-------------------------------------
 Used to retrieve data from DML stmt temporary   buffer.  * valid with "ROW" triggers only.

    INSERT   UPDATE    DELETE
 NEW    valid         valid        invalid
 OLD    invalid      valid         valid

 * They r refered with : (colon) to indicate as Bind       variables.
    They r accessed from SQL buffer to Pl/sql block.
---------------------------------------------------------------
 Triggering Events: Before insert Row,  Before update Row,
 Before delete Row
 Before insert Stmt,  Before update Stmt,
 Before delete Stmt
 After insert Row,  After update Row,
 After delete Row
 After insert Stmt,  After update Stmt,
 After delete Stmt
 12 Events per Table are allowed.
---------------------------------------------------------------
 * Modifying existing trigger create or replace Trigger up_con
 before insert on stu_info
 for each row
 begin
 if :new.course = 'oracle' and :new.fee_paid = 2500  then
 :new.sname := upper(:new.sname);     * stu_info
 else                                                       ---------- 
 :new.sname := lower(:new.sname);         roll
 end if;                                                    sname
 end;                                                       course
                                                              fee_paid
 insert into stu_info   values(111,'sekhar','oracle',2500);
 insert into stu_info   values(112,'SRINATH','oracle',1000);
 select * from stu_info;
 111 SEKHAR  oracle 2500
 112 srinath oracle 1000
---------------------------------------------------------------
* Trigger Checks for valid increment on employ          salary :>create or replace trigger chk_incr
   before update of sal on emp
   for each row
   begin
   if :new.sal <= :old.sal then
   raise_application_error(-20300,' Increment must      be more  than existing salary ');
   end if;
   end;

 >update emp set sal = sal - 3000
    where empno = 7900;
 >update emp set sal = sal - sal *.35;
---------------------------------------------------------------
 Note: Column Specification [ of sal ] is supported with   UPDATE event only.  Not valid with Insert and    Delete Operations ( Row Level Operations ).

 * Raise_application_error(error no,error message); Built_in sub program stops the DML stmt execution
 and displays the error message.
 Error No Range : -20001 to -20999
 Oracle Reserved Error Nos: -20000 to +20000
---------------------------------------------------------------
 

 * Trigger checks for null value in dept name. >create or replace trigger chk_null
   before insert or update of dname on dept
   for each row
   begin
   if :new.dname is null then
   raise_application_error(-20301,
    ' Department name cannot be blank ');
  end if;
 end;

 >insert into dept values(88,null,'Hyd');
 >update dept set dname = null
   where deptno = 30;
---------------------------------------------------------------
 Note: Constraint Vs Trigger Constraints will check for existing rows also
 Triggers will not check for existing rows

 Constraints provide standard error messages
 Triggers provide user friendly error messages

 Constraints provides pre-defined rules on table   columns.
 Triggers provides user-defined rules on table   columns

* If constraint  & Trigger are defined on table at a      time only Trigger will be activated.---------------------------------------------------------------
 > Create table job_pays
   ( job varchar2(20), losal number(10),
     hisal number(10));

 insert into job_pays values
 ('CLERK' , 3000 , 10000);
 insert into job_pays values
 ('SALESMAN' , 5000 , 15000);
 insert into job_pays values
 ('MANAGER' ,  8000  , 20000);
 insert into job_pays values
 ('ANALYST' , 10000 , 30000);
 insert into job_pays values
 ('EXECUTIVE' , 10000 , 25000);

 * Trigger Checks for valid salary according to the     Job. create or replace trigger chk_sal
 before insert on emp for each row
 -- if condition is True trigger will be executed
 when (new.job <> 'PRESIDENT') 
 declare
 minpay number(10);
 maxpay number(10);
 begin
 select losal,hisal into minpay,maxpay from      job_pays where job = :new.job;
 if :new.sal < minpay or :new.sal > maxpay then
 raise_application_error(-20200,' Salary must be    between '||minpay||' and '||maxpay||' for Job    '||:new.job);
 end if;
 exception
 when no_data_found then
 raise_application_error(-20201,' No such job exists  '||:new.job);
 end;

 INSERT INTO EMP(EMPNO,ENAME,JOB,SAL)
  VALUES(&1,'&2','&3',&4);
--------------------------------------------------------------- 

* Trigger adds the increment amount automatically    to Employ salary. ( Using IMPLICIT Cursor )                   
 create or replace trigger add_incr
 before insert on incr for each row
 begin   
   update emp set sal = sal + :new.amt
   where empno = :new.empno;
     -- Using implicit cursor
   if sql%notfound then
   raise_application_error(-20111,' No such employ     exists with employ code '||:new.empno);
   end if;
   end;

 create table incr(empno number(4), amt    number(10));
 select empno,ename,sal from emp
 where empno = 7900;
 insert into incr values(7900,4000);
 select empno,ename,sal from emp
 where empno = 7900;
 insert into incr values(799,2000);
---------------------------------------------------------------  

Student        Fee_instalments
 ---------        --------------------
 roll - 101                 roll   prev_fee   curr_fee   DOP
 name - RAM        101         0            1000         ---
 course - Oracle9i 101     1000        2000        ---
 fee - 5000           101     3000        2000        ---

 * Trigger adds the fee instalment amount            automatically into fee_instalments table .
 create or replace trigger add_fee
 before insert or update on student
 for each row
 begin
 if inserting then
 insert into fee_instalments values
 (:new.roll, 0, :new.fee, sysdate);
 elsif updating then
 insert into fee_instalments values
 (:old.roll, :old.fee, :new.fee - :old.fee, sysdate);
 end if;
 end;

 insert into student     values(101,'RAM','oracle9i',1000);
 update student set fee = fee + 2000
 where roll = 101;
 update student set fee = fee + 2000
 where roll = 101;
 select * from fee_instalments;

* Keywords:  inserting, updating, deleting 
 -- Represents insert,update and delete operations
 -- Valid in Triggers only
---------------------------------------------------------------
 Transaction -- Table
 --------------
 Accno   tran_type      amt
 101    D     5000
 102    D     7500
 103        D     1000
 104         W     1000
 105         W     9000

 * Statement Level Trigger:
 * Triggers Locks the Transaction table for update        and delete operations. ( Only insert allowed )

 create or replace trigger lock_trans
 before update or delete on transaction
 begin
 raise_application_error(-20555, ' Invalid operation   on Transaction Table ');
 end;

 delete from transaction; update transaction set amt = amt + 1000;
---------------------------------------------------------------  Disabling Triggers:
 >Alter table transaction disable all triggers;
 >Alter table bankmaster disable all triggers;

 Enabling Triggers: >Alter table transaction enable all triggers;
 >Alter table bankmaster enable all triggers;

 Removing Triggers: Drop trigger < Trigger name >;
 >Drop trigger CHK_SAL;

 Checking for Existing Triggers: >desc User_triggers
 >select  trigger_name , triggering_event from
   user_triggers where table_name = 'EMP';
 >select * from user_triggers
   where trigger_name = 'CHK_SAL';
 >select text from USER_SOURCE
    where name = 'CHK_SAL';
 > desc user_source
 

 * User_source -- system table holds the details of       Triggers .---------------------------------------------------------------
 * Statement Level Trigger:
 * Trigger Locks the table for DML operations to         provide security:
 * Trigger checks for i> Valid Timings ii> Weekends
    iii> Public Holidays

 create or replace trigger security_chk
 before insert or update or delete on Bankmaster
 declare
 a number;
 begin
 -- check for valid Timings
 if to_char(sysdate,'hh24') not in     (10,11,12,13,14,15,16,17) then
 raise_application_error(-20111,' No operations     allowed -  Invalid Timings ');
 end if;
 -- check for weekends
 if to_char(sysdate,'dy') in ('sat','sun') then
 raise_application_error(-20112,' No operations     allowed -  Weekends ');
 end if;
 -- check for Public Holidays
 select count(*) into a from holiday
 where to_date(hdate) = to_date(sysdate);
 if a > 0 then
 raise_application_error(-20113,' No operations      allowed - Public Holiday ');
 end if;
 end;

 Holiday
 --------
 hdate --- '15-aug-10'            
 description --- 'Independence Day'  
---------------------------------------------------------------
* Trigger updates Transactions automatically to       Master table.

 Bankmaster        Transaction
 -------------        --------------
 accno            accno
 name                                  tran_type
 acc_type            tran_date
 curr_bal            amt
                                            
 insert into bankmaster   values(101,'RAM','S',20000);   

 create or replace trigger add_deposit
 After insert on transaction for each row
 begin
 if :new.tran_type = 'D' then
 update bankmaster set curr_bal = curr_bal +    :new.amt  where accno = :new.accno;
 end if;
 end;

 insert into transaction   values(101,'D',sysdate,10000);
 insert into transaction   values(101,'D',sysdate,30000);
 select * from bankmaster;
 101    RAM    S   60000
---------------------------------------------------------------
 Instead of Triggers: [ 8i ]
 - Triggers supported only on "views"
 * - Used to perform DML operations on Join views
 *  Instead of  --- insert, update, delete

 Ex: 1
 >create view v1 as select * from emp;

 > create trigger t1 instead of delete on v1
    for each row
 begin
 dbms_output.put_line(' Record Removed ');
 end;

 > delete from v1 where empno = 7900;
 > select * from v1 where empno = 7900;

  create trigger t2 instead of delete on v1
    for each row
 begin
 insert into del_tab    values(:old.empno,:old.ename,sysdate,user);
 end;
---------------------------------------------------------------
 

 ** Manipulating Join views thru Instead of Triggers Ex: 2
 >create view edept as select empno, ename, sal,     job, emp.deptno, dept.deptno dno, dname, loc      from emp, dept
 where emp.deptno = dept.deptno;

 > insert into edept                  values(4411,'VIJAY',20000,'MANAGER',
   99,99,'TECHNICAL','HITECH');   --- error

 >create trigger t2 instead of insert on edept
  for each row
  begin
  insert into dept          values(:new.dno,:new.dname,:new.loc);
  insert into emp(empno,ename,sal,job,deptno)         values (:new.empno,:new.ename,:new.sal,          :new.job, :new.deptno);
  end;
 /

 >insert into edept values (......);   --- repeat step 2
 >select * from edept;
---------------------------------------------------------------
 Ex : 3
 >delete from edept where deptno = 99;
 ( Removes data from emp table only - key                  preserved table )

 * Trigger Removes records from 2 tables create or replace trigger trig11
 instead of delete on edept
 for each row
 begin
 delete from emp where deptno = :old.deptno;
 delete from dept where deptno = :old.deptno;
 end;

 delete from edept where deptno = 99; ( Removes    data from emp ,dept table also )
---------------------------------------------------------------  

Invalid Triggers : 1. >create table temp1(c1 number(3),
        c2 varchar2(10));

 2. >create trigger trig1
     before insert on temp1 for each row
     begin
      insert into temp1 values(102,'SIVA');
     end;
    /

 3. >insert into temp1 values(101,'RAM');
     Error:  It leads to an infinite loop
---------------------------------------------------------------  

>create trigger trig2
   before insert on temp1 for each row
   begin
   update temp1 set c2 = 'HARI';
   end;

 >insert into temp1 values (102,'RAVI');

 It will not perform update in Trigger body becoz 2    DML operations cannot be performed on table at      once.  It leads to a " Trigger Mutuation " error   while working in other s/w tools.
---------------------------------------------------------------  

Note:
 **  TCL commands are not allowed in Triggers.
 Becoz Trigger will be executed while  performing     DML stmt but TCL has to be given after DML stmt    execution.
---------------------------------------------------------------  

Advantage of Database Triggers:  - Automatic execution of code based on event .
  - Used to impose user defined restrictions on             Tables .
  - Provides Security on Tables while Manipulating        data from any where .
---------------------------------------------------------------