1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Database Triggers in PL/SQL

Discussion in 'SQL PL/SQL' started by SBH, Dec 15, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Overview

    Triggers are named PL/SQL subprograms which are invoked automatically by Oracle at predefined timing and event. Timing and event are set in the trigger definition. Similar to other subprograms, triggers are physically stored in database.

    Triggers prove to be immensely useful in ensuring security and auditing. It also maintains data integrity and alternatively referential integrity too.

    2. Database Triggers and Application Triggers

    Based on scope of usage of triggers, Triggers can be categorized as Database triggers and Application triggers. Database triggers are activated on any event occurring in the database, while application trigger are restricted to an application.

    Database triggers can be created on top of table, view, schema or database. Timings for table or view can be before and after a DML operation, while those on schema and database can be logging in and log off.

    3. DML Triggers

    Below are the three criteria which must be kept in mind before creating a DML trigger
    • There can be three possible DML actions on data i.e. INSERT, UPDATE or DELETE. These are events for the DML triggers.
    • An alternative or simultaneous action can be performed either before or after an event. This serves as timing for DML triggers.
    • Whether the trigger action must be at DML statement level or at affected row level, decides the level of a trigger.

    Once the timing, event and level are set, trigger body must be created to implement the triggering logic.

    Note: The size of a trigger cannot be greater than 32 KB.

    4. Syntax

    Code (Text):
    CREATE [OR REPLACE] TRIGGER [TRIGGER NAME]
    TIMING [BEFORE | AFTER]
    EVENT [INSERT | UPDATE | DELETE]
    ON OBJECT_NAME
    [REFERENCING OLD AS OLD | NEW AS NEW]
    [FOR EACH ROW]
    WHEN (CONDITION)
    DECLARE
    BEGIN
    ... TRIGGER_BODY
    [EXCEPTION . . .]
    END [TRIGGER NAME];
    In the syntax, note that REFERENCING clause is valid only for row level triggers. By default, a trigger is created at statement level.
    WHEN clause is used to impose condition based triggering action. Majorly, it is used with row level triggers only.

    5. Statement Level vs. Row Level triggers

    [TABLE]Statement-Level Triggers Row-Level Triggers
    Default trigger FOR EACH ROW specification is required
    One time firing when the triggering event occurs Fires for all the rows affected by an event
    Even if no rows are affected, it fires If no rows are affected, it will not fire[/TABLE]

    [​IMG]

    6. Creating a DML Statement Trigger

    We would create a statement level trigger as below. The trigger fires before the INSERT action on EMP table. As per the trigger action, it inserts a record in Employee Log table, with current date, action and remarks.

    Code (SQL):
    CREATE OR REPLACE TRIGGER T_TRACE_EMP
    BEFORE INSERT ON EMPLOYEE
    BEGIN

    INSERT INTO EMP_TRACE
     (ID, STATUS, ACTION_DATE, ACTION, REMARKS)
    VALUES
     (LOG.NEXTVAL, 'P', SYSDATE, 'CREATE', 'Welcome to CLUB ORACLE');

    END;

    TRIGGER created.
     
    Code (SQL):
    SQL> INSERT INTO EMPLOYEE (EMPID, ENAME, SALARY, DEPTNO)
    VALUES
    (SQ_EMPID.NEXTVAL, 'MIKE', 4500, 20);

    1 ROW inserted.

    SQL> SELECT * FROM EMP_TRACE;

    ID  STAT    ACTION_DATE ACTION  REMARKS
    --- ----    ------------    ------  -----------------------------
    16  P   11-OCT-2010 INSERT      Welcome TO CLUB ORACLE
    7. Conditional Predicates to Detect the Trigger DML operation

    For a specific timing, if all the events have to be tested instead of creating three different DML triggers, oracle provides DML predicates to be used in trigger body. The available predicates can be INSERTING, UPDATING or DELETING. For example, below trigger body shows the usage of DML predicates. Note the event specification and handling.

    Code (SQL):
    CREATE OR REPLACE TRIGGER T_EMP_TRACE
    BEFORE INSERT OR UPDATE OR DELETE ON EMP
    BEGIN

    IF INSERTING THEN

    ELSIF UPDATING THEN

    ELSIF DELETING

    END IF;
    END;

    TRIGGER created.
     
    8. Creating a DML Row Level Trigger

    The DML row level trigger T_EMP_MEMBERSHIP deletes the membership record for every employee record which gets deleted.

    Code (SQL):
    CREATE OR REPLACE TRIGGER T_EMP_MEMBERSHIP
    BEFORE DELETE ON EMPLOYEE
    BEGIN
    FOR EACH ROW
     DELETE FROM EMP_MEMBERSHIP
     WHERE EMPID=:OLD.EMPID;
    END;

    TRIGGER created.
    8.1. OLD and NEW identifiers

    The OLD and NEW are identifiers which carry a record value before and after the DML event. The record values can be referred by prefixing a column value with the corresponding identifier :)OLD or :NEW).

    Below table shows the OLD and NEW values within each triggering event.

    [TABLE]Event OLD value NEW value
    INSERT NULL Current value
    UPDATE Old value of record New value of record
    DELETE Record value before delete operation NULL[/TABLE]

    Illustration

    The trigger below archives an employee record if salary is incremented by more than 1000. Note that the increment is checked by the WHEN clause condition.

    Code (SQL):
    CREATE OR REPLACE TRIGGER T_EMP_ARCH
    BEFORE UPDATE OF SALARY ON EMPLOYEE
    FOR EACH ROW
    WHEN (OLD.SAL – NEW.SAL > 1000)
    BEGIN

    INSERT INTO EMP_ARCHIVE (ID, EMPLOYEE_ID, OLD_SAL, NEW_SAL, REVISED_DT)
    VALUES
    (SQ_ARC.NEXTVAL, :OLD.EMPID,:OLD.SALARY, :NEW.SALARY, SYSDATE);

    END;
    8. INSTEAD OF Triggers

    Triggers on views are known as INSTEAD OF triggers. They are known by their name because they skip the current triggering event action and perform alternate one. Other reason could be that only timing mode available in such triggers is INSTEAD OF. It is required for the complex view because it is based on more than one table. Any DML on complex view would be successful only if all key columns, not null columns are selected in the view definition. Alternatively, INSTEAD OF trigger can be created to synchronize the effect of DML across all the tables.

    Instead of trigger is a row level trigger and can be used only with a view, and not with tables. For example, below view ORDER_VU is created on top of ORDERS and WAREHOUSE tables. If RET_LIMIT is attempted for update in the view, a record must be added to WAREHOUSE_HISTORY table and new value must be updated in the WAREHOUSE table.

    Code (SQL):
    CREATE OR REPLACE VIEW ORDER_VU
    AS
    SELECT O.ID, O.QTY, O.ORD_DATE, P.SITE_ID, P.RET_LIMIT
    FROM ORDERS O, WAREHOUSE P
    WHERE O.SITE_ID=P.SITE_ID


    CREATE OR REPLACE TRIGGER T_UPD_ORDERVIEW
    INSTEAD OF UPDATE ON ORDER_VU
    BEGIN
      INSERT INTO WAREHOUSE_HISTORY
      (SITE_ID, OLD_RET_LIMIT, NEW_RET_LIMIT, UPD_DATE, UPD_USER)
      VALUES
      (:OLD.SITE_ID, :OLD_RET_LIMIT, :NEW.RET_LIMIT, SYSDATE, USER);

      UPDATE WAREHOUSE
      SET RET_LIMIT = :NEW.RET_LIMIT
      WHERE SITE_ID = :OLD.SITE_ID;
    END;
    /

    10. Syntax for recompiling a Trigger

    Code (SQL):
    ALTER TRIGGER trigger_name COMPILE;
    11. Syntax for removing Triggers

    Code (SQL):
    DROP TRIGGER trigger_name;
     

    Attached Files: