Scribd Logo
Upload

Welcome to Scribd!

  • 302 views

    DML Triggers

    Uploaded by

    Mouniga Ve
    Database triggers allow code to be automatically executed in response to events like data modifications. Triggers can be defined at the statement or row level. Statement triggers execute once per triggering event, while row triggers execute once for each row affected. Triggers use the OLD and NEW pseudorecords to access pre- and post-modification data values. The order of execution for multiple triggers on the same event is defined. Conditional predicates and the WHEN clause allow triggers to respond selectively to different DML operations or row values. Restrictions apply to transactions within triggers unless declared autonomous. Triggers can be disabled, dropped, and queried through data dictionary views.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd

    DML Triggers

    Uploaded by

    Mouniga Ve
    302 views22 pages
    Database triggers allow code to be automatically executed in response to events like data modifications. Triggers can be defined at the statement or row level. Statement triggers execute once per triggering event, while row triggers execute once for each row affected. Triggers use the OLD and NEW pseudorecords to access pre- and post-modification data values. The order of execution for multiple triggers on the same event is defined. Conditional predicates and the WHEN clause allow triggers to respond selectively to different DML operations or row values. Restrictions apply to transactions within triggers unless declared autonomous. Triggers can be disabled, dropped, and queried through data dictionary views.

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Database triggers allow code to be automatically executed in response to events like data modifications. Triggers can be defined at the statement or row level. Statement triggers execute once per triggering event, while row triggers execute once for each row affected. Triggers use the OLD and NEW pseudorecords to access pre- and post-modification data values. The order of execution for multiple triggers on the same event is defined. Conditional predicates and the WHEN clause allow triggers to respond selectively to different DML operations or row values. Restrictions apply to transactions within triggers unless declared autonomous. Triggers can be disabled, dropped, and queried through data dictionary views.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    302 views22 pages

    DML Triggers

    Uploaded by

    Mouniga Ve
    Database triggers allow code to be automatically executed in response to events like data modifications. Triggers can be defined at the statement or row level. Statement triggers execute once per triggering event, while row triggers execute once for each row affected. Triggers use the OLD and NEW pseudorecords to access pre- and post-modification data values. The order of execution for multiple triggers on the same event is defined. Conditional predicates and the WHEN clause allow triggers to respond selectively to different DML operations or row values. Restrictions apply to transactions within triggers unless declared autonomous. Triggers can be disabled, dropped, and queried through data dictionary views.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 22

    DML Triggers

    A. Anusooya Devi, MIT

    Database Triggers
    Triggers are named PL/SQL blocks with declarative, executable, and exception handling sections A trigger is executed implicitly whenever the triggering event takes place Triggers do not accept arguments Like packages, triggers are stored database objects and can not be local to a block
    A. Anusooya Devi, MIT

    Stored Procedures Vs Database Triggers


    Stored Procedures A procedure is called explicitly by a user, application or a trigger Can not be inactive Can have parameters No restriction on the size of a procedure Triggers A trigger executes implicitly in response to an event, such as an update operation on a table We can enable and disable according to the need Can not have parameters There may be a restriction on the size of a trigger depending on the version of oracle used

    A. Anusooya Devi, MIT

    DML Triggers
    Application:
    To impose complex integrity constraints not possible through declarative constraints To audit information in a table To create replica of a table etc. Security reasons

    A. Anusooya Devi, MIT

    Types of Triggers
    DML Triggers
    DML Triggers are fired by the execution of DML statements

    DDL Triggers
    DDL triggers execute every time a DDL statement is executed

    Database Triggers
    Database triggers are fired during system events like startup/shutdown and user events like logon/logoff.
    A. Anusooya Devi, MIT

    DML Triggers
    DML Trigger Components
    Part
    Trigger Timing

    Description
    When the trigger fires in relation to the triggering event Which data manipulation operation on the table or view, causes the trigger to fire How many times the trigger body executes What action the trigger performs
    A. Anusooya Devi, MIT

    Possible Values
    BEFORE AFTER INSERT UPDATE DELETE Statement Row Complete PL/SQL Block

    Triggering Event

    Trigger Type

    Trigger Body

    Statement Triggers
    The trigger body executes only once for the triggering event. This is the default. Syntax:
    CREATE [ OR REPLACE ] TRIGGER trigger_name trigger_timing event1 [ OR event2 OR event3 ] ON table_name PL/SQL Block;
    A. Anusooya Devi, MIT

    Statement Triggers: Example


    CREATE OR REPLACE TRIGGER chk_time BEFORE INSERT OR UPDATE OR DELETE ON emp BEGIN IF (TO_CHAR(sysdate,DY) IN (SAT,SUN)) OR (TO_CHAR(sysdate,HH24) NOT BETWEEN 08 AND 18) THEN RAISE_APPLICATION_ERROR(-20400, You can not perform any DML Operation); END IF; END;

    A. Anusooya Devi, MIT

    Row Triggers
    The trigger body executes once for each row affected by the triggering event. Syntax for creating Row Triggers:
    CREATE [ OR REPLACE ] TRIGGER trigger_name trigger_timing event1 [ OR event2 OR event3 ] ON table_name FOR EACH ROW [WHEN condition] PL/SQL Block;

    FOR EACH ROW: Designates the trigger to be a row trigger WHEN condition: Specifies the trigger restriction For the UPDATE event there is an optional clause
    [OF column_name[,column_name..]]

    A. Anusooya Devi, MIT

    Using OLD and NEW Qualifiers


    In a ROW LEVEL trigger all the column values of the current row, before modification and after modification, are available to the trigger block as local variables. To access these values, the OLD and NEW quantifiers are used e.g. :OLD.empno, :NEW.deptno

    Data Operation INSERT UPDATE DELETE NULL

    Old Value

    New Value Inserted Value Value after update NULL

    Value before update Value before delete

    A. Anusooya Devi, MIT

    Example - :old and :new variables


    CREATE TABLE emp_hist ( empno NUMBER(5), oldSal NUMBER(10), newSal NUMBER(10)); CREATE OR REPLACE TRIGGER LOG_TRIG AFTER UPDATE OF sal ON emp FOR EACH ROW BEGIN IF :NEW.sal < :OLD.sal THEN RAISE_APPLICATION_ERROR(-20101,Salary cannot be decremented); ELSE INSERT INTO emp_hist VALUES (:OLD.empno, :OLD.sal, :NEW.sal); END IF; END;

    A. Anusooya Devi, MIT

    Sequence of execution of Triggers


    Multiple triggers can be created on the same table for the same event. The order of execution is as follows.
    1. Execute all BEFORE STATEMENT triggers. 2. For each row in the target table: (a) Execute all BEFORE ROW triggers. (b) Execute the DML statement and perform integrity constraint checking (c) Execute all AFTER ROW triggers. 3. Execute all AFTER STATEMENT triggers.

    A. Anusooya Devi, MIT

    Example - Sequence of execution of Triggers


    CREATE OR REPLACE TRIGGER t1 BEFORE UPDATE ON emp BEGIN DBMS_OUTPUT.PUT_LINE('This is statement level before update trigger'); END; CREATE OR REPLACE TRIGGER t2 BEFORE UPDATE ON emp FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE('This is row level before update trigger'); END; CREATE OR REPLACE TRIGGER t3 AFTER UPDATE ON emp FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE('This is row level after update trigger'); END; CREATE OR REPLACE TRIGGER t4 AFTER UPDATE ON emp BEGIN DBMS_OUTPUT.PUT_LINE('This is statement level after update trigger'); END;

    A. Anusooya Devi, MIT

    Conditional Predicates
    You can combine several triggering events in one trigger To identify which event has raised the trigger, use conditional predicates:
    INSERTING UPDATING DELETING

    They return a TRUE value depending upon the DML statement executed
    A. Anusooya Devi, MIT

    Example - Conditional Predicates


    CREATE TABLE audit_table (user_name VARACHAR2(10), table_name VARCHAR2(10), ins NUMBER(4), del NUMBER(4), upd NUMBER(4)); INSERT INTO audit_table VALUES(USER,EMP,0,0,0);

    CREATE OR REPLACE TRIGGER audit_emp AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW BEGIN IF INSERTING THEN UPDATE audit_table SET ins = ins + 1 WHERE user_name = USER AND table_name= EMP; ELSIF DELETING THEN UPDATE audit_table SET del = del + 1 WHERE user_name = USER AND table_name= EMP; ELSIF UPDATING THEN UPDATE audit_table SET upd = upd + 1 WHERE user_name = USER AND table_name= EMP; END IF;

    END;

    A. Anusooya Devi, MIT

    WHEN clause
    Valid for row triggers only Trigger body executes for those rows that meet the condition Evaluates for each row OLD and NEW variables can be referenced here For OLD and NEW variables we should not use colon (:) in the WHEN condition
    A. Anusooya Devi, MIT

    WHEN clause
    CREATE OR REPLACE TRIGGER TRIG_WHEN AFTER UPDATE OF sal ON emp FOR EACH ROW WHEN (old.sal > new.sal) BEGIN RAISE_APPLICATION_ERROR(-20009, Cannot reduce salary); END;
    A. Anusooya Devi, MIT

    Restrictions on a Trigger
    By default, Transaction Control Language commands like COMMIT or ROLLBACK are not allowed within a trigger body E. g. CREATE TRIGGER trig AFTER INSERT ON emp BEGIN INSERT INTO emp_log VALUES (SYSDATE, 'Insert on emp'); COMMIT; END; INSERT INTO emp(empno) VALUES (1); INSERT INTO emp(empno) VALUES (1) * ERROR at line 1: ORA-04092: cannot COMMIT in a trigger ORA-06512: at "SCOTT.TAB1_TRIG", line 3 ORA-04088: error during execution of trigger 'SCOTT.TAB1_TRIG'

    A. Anusooya Devi, MIT

    Use of TCL Commands in a Trigger


    CREATE OR REPLACE TRIGGER tab1_trig AFTER insert ON tab1 DECLARE -- declare the trigger as separate transaction from the -- triggering event PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO emp_log VALUES (SYSDATE, 'Insert on COMMIT; -- allowed only in autonomous triggers END;

    emp');

    SQL> INSERT INTO emp(empno) VALUES (1); 1 row created.


    A. Anusooya Devi, MIT

    Managing Triggers
    Disable / Enable trigger: ALTER TRIGGER trigger_name ENABLE/DISABLE; Disable / Enable all triggers for a table: ALTER TABLE table_name DISABLE/ENABLE ALL TRIGGERS; Dropping Triggers: DROP TRIGGER trigger_name; USER_TRIGGERS data dictionary view: To query database trigger details stored in a database dictionary

    A. Anusooya Devi, MIT

    Laboratory Exercise
    Q)Create a trigger named TR_CHECK_COMM to implement the following business rule. In EMP table, employee having job as Salesman should receive a commission. A Salesman must receive a commission of not less than Rs. 100. Employees who are not sales persons are not entitled to get commission (comm value should be NULL). Test it by inserting a record in the emp table.
    A. Anusooya Devi, MIT

    THANK YOU

    A. Anusooya Devi, MIT

    You might also like