Wednesday 22 October 2014

Trigger : One Source all info

Trigger (Last Updated on  23-Oct-2014)



What is a trigger  ?

ans : Triggers are procedures that are stored in the database and are implicitly run, or fired, when something happens.




Classification tree of triggers ?


ans: Triggers can be of following types  :




  • DML triggers on tables.
  • INSTEAD OF triggers on views.
  • System triggers on DATABASE or SCHEMA: With DATABASE, triggers fire for each event for all users; with SCHEMA,                                                                 triggers fire for each event for that specific user.

  • But more convenient way of classifying triggers is on the basis of Events on which they are fired  :

    • DML statements (DELETEINSERTUPDATE)
    • DDL statements (CREATEALTERDROP)
    • Database operations (SERVERERRORLOGONLOGOFFSTARTUPSHUTDOWN)
                
    Now Creating triggers  ?

    8 common types of trigger are as follow :

    a) Before Insert Trigger 


    Syntax : 

    CREATE [ OR REPLACE ] TRIGGER trigger_name
    BEFORE INSERT
       ON table_name
       [ FOR EACH ROW ]
    
    DECLARE
       -- variable declarations
    
    BEGIN
       -- trigger code
    
    EXCEPTION
       WHEN ...
       -- exception handling
    
    END;

    Example : 
    CREATE TABLE orders
    ( order_id number(5),
      quantity number(4),
      cost_per_item number(6,2),
      total_cost number(8,2),
      create_date date,
      created_by varchar2(10)
    );
    CREATE OR REPLACE TRIGGER orders_before_insert
    BEFORE INSERT
       ON orders
       FOR EACH ROW
       
    DECLARE
       v_username varchar2(10);
       
    BEGIN
    
       -- Find username of person performing INSERT into table
       SELECT user INTO v_username
       FROM dual;
       
       -- Update create_date field to current system date
       :new.create_date := sysdate;
       
       -- Update created_by field to the username of the person performing the INSERT
       :new.created_by := v_username;
       
    END;
    When would i like to use and when not  ?




  • You can not create a BEFORE trigger on a view.
  • You can update the :NEW values.
  • You can not update the :OLD values.

  • B) After Insert Trigger 

    Syntax 
    CREATE [ OR REPLACE ] TRIGGER trigger_name
    AFTER INSERT
       ON table_name
       [ FOR EACH ROW ]
    
    DECLARE
       -- variable declarations
    
    BEGIN
       -- trigger code
    
    EXCEPTION
       WHEN ...
       -- exception handling
    
    END;
    Example :
    CREATE TABLE orders
    ( order_id number(5),
      quantity number(4),
      cost_per_item number(6,2),
      total_cost number(8,2)
    );
    CREATE OR REPLACE TRIGGER orders_after_insert
    AFTER INSERT
       ON orders
       FOR EACH ROW
       
    DECLARE
       v_username varchar2(10);
       
    BEGIN
       
       -- Find username of person performing the INSERT into the table
       SELECT user INTO v_username
       FROM dual;
       
       -- Insert record into audit table
       INSERT INTO orders_audit
       ( order_id,
         quantity,
         cost_per_item,
         total_cost,
         username )
       VALUES
       ( :new.order_id,
         :new.quantity,
         :new.cost_per_item,
         :new.total_cost,
         v_username );
         
    END;
    When would i like to use and when not  ?




  • You can not create an AFTER trigger on a view.
  • You can not update the :NEW values.
  • You can not update the :OLD values.

  • C) Before Update Trigger

    Syntax 
    CREATE [ OR REPLACE ] TRIGGER trigger_name
    BEFORE UPDATE
       ON table_name
       [ FOR EACH ROW ]
    
    DECLARE
       -- variable declarations
    
    BEGIN
       -- trigger code
    
    EXCEPTION
       WHEN ...
       -- exception handling
    
    END;
    Example
    CREATE TABLE orders
    ( order_id number(5),
      quantity number(4),
      cost_per_item number(6,2),
      total_cost number(8,2),
      updated_date date,
      updated_by varchar2(10)
    );
    CREATE OR REPLACE TRIGGER orders_before_update
    BEFORE UPDATE
       ON orders
       FOR EACH ROW
       
    DECLARE
       v_username varchar2(10);
       
    BEGIN
    
       -- Find username of person performing UPDATE on the table
       SELECT user INTO v_username
       FROM dual;
       
       -- Update updated_date field to current system date
       :new.updated_date := sysdate;
       
       -- Update updated_by field to the username of the person performing the UPDATE
       :new.updated_by := v_username;
       
    END;
    When would i like to use and when not  ?




  • You can not create a BEFORE trigger on a view.
  • You can update the :NEW values.
  • You can not update the :OLD values.


  • D) After Update Trigger

    Syntax 
    CREATE [ OR REPLACE ] TRIGGER trigger_name
    AFTER UPDATE
       ON table_name
       [ FOR EACH ROW ]
    
    DECLARE
       -- variable declarations
    
    BEGIN
       -- trigger code
    
    EXCEPTION
       WHEN ...
       -- exception handling
    
    END;
    Example
    CREATE TABLE orders
    ( order_id number(5),
      quantity number(4),
      cost_per_item number(6,2),
      total_cost number(8,2)
    );
    CREATE OR REPLACE TRIGGER orders_after_update
    AFTER UPDATE
       ON orders
       FOR EACH ROW
       
    DECLARE
       v_username varchar2(10);
       
    BEGIN
    
       -- Find username of person performing UPDATE into table
       SELECT user INTO v_username
       FROM dual;
       
       -- Insert record into audit table
       INSERT INTO orders_audit
       ( order_id,
         quantity_before,
         quantity_after,
         username )
       VALUES
       ( :new.order_id,
         :old.quantity,
         :new.quantity,
         v_username );
         
    END;
    When would i like to use and when not  ?





  • You can not create an AFTER trigger on a view.
  • You can not update the :NEW values.
  • You can not update the :OLD values.

  • E) Before Delete Trigger


    Syntax
    CREATE [ OR REPLACE ] TRIGGER trigger_name
    BEFORE DELETE
       ON table_name
       [ FOR EACH ROW ]
    
    DECLARE
       -- variable declarations
    
    BEGIN
       -- trigger code
    
    EXCEPTION
       WHEN ...
       -- exception handling
    
    END;
    Example 


    CREATE TABLE orders
    ( order_id number(5),
      quantity number(4),
      cost_per_item number(6,2),
      total_cost number(8,2)
    );
    CREATE OR REPLACE TRIGGER orders_before_delete
    BEFORE DELETE
       ON orders
       FOR EACH ROW
       
    DECLARE
       v_username varchar2(10);
       
    BEGIN
    
       -- Find username of person performing the DELETE on the table
       SELECT user INTO v_username
       FROM dual;
       
       -- Insert record into audit table
       INSERT INTO orders_audit
       ( order_id,
         quantity,
         cost_per_item,
         total_cost,
         delete_date,
         deleted_by )
       VALUES
       ( :old.order_id,
         :old.quantity,
         :old.cost_per_item,
         :old.total_cost,
          sysdate,
          v_username );
          
    END;
    When would i like to use and when not  ?




  • You can not create a BEFORE trigger on a view.
  • You can update the :NEW values.
  • You can not update the :OLD values.

  • F) After Delete Trigger 

    Syntax 
    CREATE [ OR REPLACE ] TRIGGER trigger_name
    AFTER DELETE
       ON table_name
       [ FOR EACH ROW ]
    
    DECLARE
       -- variable declarations
    
    BEGIN
       -- trigger code
    
    EXCEPTION
       WHEN ...
       -- exception handling
    
    END;
    Example 
    CREATE TABLE orders
    ( order_id number(5),
      quantity number(4),
      cost_per_item number(6,2),
      total_cost number(8,2)
    );
    CREATE OR REPLACE TRIGGER orders_after_delete
    AFTER DELETE
       ON orders
       FOR EACH ROW
    
    DECLARE
       v_username varchar2(10);
       
    BEGIN
    
       -- Find username of person performing the DELETE on the table
       SELECT user INTO v_username
       FROM dual;
       
       -- Insert record into audit table
       INSERT INTO orders_audit
       ( order_id,
         quantity,
         cost_per_item,
         total_cost,
         delete_date,
         deleted_by)
       VALUES
       ( :old.order_id,
         :old.quantity,
         :old.cost_per_item,
         :old.total_cost,
         sysdate,
         v_username );
         
    END;

    When would i like to use and when not  ?





  • You can not create an AFTER trigger on a view.
  • You can not update the :NEW values.
  • You can not update the :OLD values.



  • How to find current triggers in oracle  ?

    ans: Following data distcionary are present in oracle for  viewing triggers information  . These are like tables so user can select from these tables to find information  .
    • USER_TRIGGERS
    • ALL_TRIGGERS
    • DBA_TRIGGERS


    Where do trigger stores ?

    ans: Triggers are stored in the data dictionary, within the SYSTEM tablespace. Their names and some properties are visible through views like dba_triggers (and all_triggers and user_triggers) and additionally through views dba_objects (and all_objects and user_objects) where object_type = 'TRIGGER'.

    Is there any size limit on triggers  ?
    ans :  
     The size of the trigger cannot be more than 32K.
    According to the Oracle Reference guide, the limit is imposed by the tool executing the stored procedure or trigger, and can be between 2000 and 3000 lines. It does not say how many characters or bytes this equates to, although non-stored SQL statements are limited to 64K characters. (But this statement is  for oracle 9)


    What is most efficient way of creating long  triggers  ?

    ans : The best way to write long triggers is to use them to call required procedures . They should be written to call procedures from packages which have procedures that do actual work

    What points to be taken into consideration before planning to create trigger  ?


    ans : guidelines when designing your triggers:
    • Use triggers to guarantee that when a specific operation is performed, related actions are performed.

    • Do not define triggers that duplicate features already built into Oracle Database. For example, do not define triggers to reject bad data if you can do the same checking through declarative integrity constraints.

    • Limit the size of triggers. If the logic for your trigger requires much more than 60 lines of PL/SQL code, it is better to include most of the code in a stored procedure and call the procedure from the trigger.

    • Use triggers only for centralized, global operations that should be fired for the triggering statement, regardless of which user or database application issues the statement.

    • Do not create recursive triggers. For example, creating an AFTER UPDATE statement trigger on the Emp_tab table that itself issues an UPDATE statement on Emp_tab, causes the trigger to fire recursively until it has run out of memory.

    • Use triggers on DATABASE judiciously. They are executed for every user every time the event occurs on which the trigger is created.






      UR Comments are invaluable for us . Please provide them  . I like to welcome questions that U need to find answer on this topic ..... next update coming soon