Oracle DML Triggers

DML Triggers

DML triggers:
  • Simple
  • Compound
    • can fire at one, some, or all of the preceding timing points
  • INSTEAD OF
    • Created on a noneditioning view, or on a nested table column of a noneditioning view.
    • Used to perform a DML action on the underlying tables of a view.
    • The database fires the INSTEAD OF trigger instead of running the triggering DML statement.
  • Crossedition
    • For use only in edition-based redefinition

  • Triggering event: DELETE, INSERT, and UPDATE.
  • Simple DML trigger fires at exactly one of these timing points:
  • Before the triggering statement runs (BEFORE trigger).
  • After the triggering statement runs (AFTER trigger).
    • Statement triggers are always executed, independently of whether the triggering event actually affected any rows in the underlying table.
    • Statement-level triggers are also known as table-level triggers.
    • You cannot use a WHEN clause in a statement-level trigger.
    • You also cannot reference the new or old pseudo-records in a statement-level trigger. Doing so results in a compile-time error (ORA-04082: NEW or OLD references not allowed in table level triggers).
  • Before each row that the triggering statement affects (BEFORE each row trigger).
  • After each row that the triggering statement affects (AFTER each row trigger).
    • If the DML statement does not affect any row the trigger is NOT executed.

DML Triggers and DCL (Commit, Rollback, Savepoint)
  • You cannot use SQL Data Control Language (DCL) in them, unless you declare the trigger as autonomous.
  • When triggers run inside the scope of a transaction, they disallow setting a SAVEPOINT or performing either a ROLLBACK or COMMIT statement.
  • Also, No function or procedure called by a trigger can have a DCL statement in their execution path.

DML Trigger: Syntax
CREATE [OR REPLACE] TRIGGER trigger_name
 {BEFORE | AFTER}
 {INSERT | UPDATE | UPDATE OF column1 [, column2 [, column(n+1)]] | DELETE}
 ON table_name
 [FOR EACH ROW]
 [WHEN (logical_expression)]
[DECLARE]
  [PRAGMA AUTONOMOUS_TRANSACTION;]
  declaration_statements;
BEGIN
  execution_statements;
END [trigger_name];
/

Trigger firing sequence for any DML (I/U/D)
  1. Before DML FOR EACH STATEMENT.
  2. Before DML FOR EACH ROW.
  3. DML statement executed.
  4. After DML FOR EACH ROW.
  5. After DML FOR EACH STATEMENT.


(?) How many triggers can you have for INSERT on Employees?
  • No restrictions. You can create many for a single event.
  • However, the ORDER of firing is not guaranteed.
  • Consolidate triggers, if sequence in important.

 Before UPDATE Statement-level Triggers
  • Useful to ensure integrity rules (i.e. new value should be within some range) or business requirements (i.e. ensure that user has required privileges for operation).
  • Can be also used to check whether the triggering event should be allowed to complete successfully. If the statement is found to be not authorized, the trigger could, for example, log it on an audit table, send notification messages, and raise an exception so that the statement fails. 

SQL> CREATE or REPLACE TRIGGER audit_emp_hours
 BEFORE INSERT OR UPDATE OR DELETE 
 ON employees
BEGIN
  IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
        OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
    RAISE_APPLICATION_ERROR (-20205,
		'You may only make changes during normal office hours');
  END IF;
END;
/
Trigger Created.
SQL> 

SQL> update employees set salary=43000 where employee_id=210;
update employees set salary=43000 where employee_id=210
         *
ERROR at line 1:
ORA-20205: You may only make changes during normal office hours
ORA-06512: at "HR.AUDIT_EMP_HOURS", line 4
ORA-04088: error during execution of trigger 'HR.AUDIT_EMP_HOURS'


 Using CONDITIONAL PREDICATES to audit actions/events:
  • INSERING
  • UPDATING
  • DELETING
 { TRUE | FALSE }

The trigger below writes into an audit table (AUDITBL) when any DML statement is executed on the EMPLOYEES table.

(a) Create the Audit table (AUDITBL) 

SQL> CREATE TABLE AudiTbl ( 
     id_audit NUMBER  NOT NULL , 
     username VARCHAR2 (20) , 
     action VARCHAR2 (400) , 
     dt_action DATE , 
     table_name VARCHAR2 (100) , 
     old_value VARCHAR2 (400) , 
     new_value VARCHAR2 (400) 
    );
Table created.

SQL>ALTER TABLE AudiTbl 
    ADD CONSTRAINT "AudiTblPK" PRIMARY KEY (id_audit);
Table altered.

SQL> CREATE SEQUENCE seq_audit START WITH 1;
Sequence created.
SQL>

(b) Create Conditional AFTER UPDATE/INSERT/DELETE Trigger on (EMPLOYEES).

SQL> Set Serveroutput on;

SQL> CCREATE OR REPLACE TRIGGER audit_employees
 AFTER
    INSERT OR
    UPDATE OF salary OR
    UPDATE OF department_id OR
    DELETE
  ON employees
  FOR EACH ROW
BEGIN
  CASE
    WHEN INSERTING THEN
      INSERT into AudiTbl VALUES
       (seq_audit.nextval, user, 'INSERT', sysdate, 'employees', null, null);
      DBMS_OUTPUT.PUT_LINE('Inserting on employees');
    WHEN UPDATING('salary') THEN
      DBMS_OUTPUT.PUT_LINE('Updating salary');
      INSERT into AudiTbl VALUES
       (seq_audit.nextval, user, 'UPDATE salary', sysdate, 'employees', 
        :OLD.salary, :NEW.salary);
    WHEN UPDATING('department_id') THEN
      DBMS_OUTPUT.PUT_LINE('Updating department ID');
      INSERT into AudiTbl VALUES
       (seq_audit.nextval, user, 'UPDATE Dept_id', sysdate, 'employees', 
        :OLD.department_id, :NEW.department_id);
    WHEN DELETING THEN
      DBMS_OUTPUT.PUT_LINE('Deleting');
      INSERT into AudiTbl VALUES
       (seq_audit.nextval, user, 'DELETE', sysdate, 'employees', 
        :OLD.employee_id, null);
  END CASE;
END;
/

Notes:
  • OLD, NEW and PARENT are correlation names. :OLD and :NEW qualifiers can only be used in row level triggers. These reference the old and new values of a column, respectively.







(c) Test the INSERT trigger on (EMPLOYEES).

SQL> insert into employees (employee_id, first_name, last_name, email, 
         phone_number, hire_date, job_id, salary, commission_pct, 
         manager_id, department_id)
values (employees_seq.nextval, 'John', 'Fergunson', 'email@email.com', 
        null, sysdate, 'IT_PROG', 20000, null, null, null);
Inserting on employees

1 row created.
SQL>
SQL> Select * from auditbl;

ID_AUDIT USERNAME ACTION DT_ACTION TABLE_NAME OLD_VALUE NEW_VALUE
1 JOHNF INSERT 18-AUG-10 employees    

(d) Test the UPDATE (salary) trigger

SQL> update employees set salary=salary*2 where employee_id=210;
Updating salary

1 row updated.
SQL>
SQL> Select * from auditbl;

ID_AUDIT USERNAME ACTION DT_ACTION TABLE_NAME OLD_VALUE NEW_VALUE
1 JOHNF INSERT 18-AUG-10 employees    
2 MARCJ UPDATE 11-Sep-10 employees 15000 30000



Row-Level Update Trigger
  • The trigger below is fired when an employee is assigned to the department 10.
  • It fires AFTER an UPDATE on emploees, but ONLY WHEN the update was on the department_id COLUMN AND the new value for that column is set to 10.
  • Note that in the WHEN clause you use NEW. In the trigger bodyyou use :NEW.
  • Inside a trigger body, you preface the pseudo-records with a colon (:). The colon let you reference the externally scoped pseudo-records in the trigger body.

SQL> CREATE OR REPLACE TRIGGER audit_dept10
  AFTER UPDATE OF department_id
  ON employees
  FOR EACH ROW
  WHEN (new.department_id = 10)
BEGIN
  DBMS_OUTPUT.PUT_LINE('Update on employees');
  INSERT into AudiTbl VALUES
    (seq_audit.nextval, user, 'UPDATE', sysdate, 'employees', 
        :old.department_id, :new.department_id);
END;
/

Trigger created.
SQL> set serveroutput on
SQL> update employees 
        set department_id = 10 
        where employee_id in (201, 202, 203, 210);

4 rows updated.
Update on employees
Update on employees
Update on employees
Update on employees

 
SQL> select * from auditbl;

ID_AUDIT USERNAME ACTION DT_ACTION TABLE_NAME OLD_VALUE NEW_VALUE
18 JAFFN UPDATE 26-AUG-11 employees 70 10
18 JAFFN UPDATE 26-AUG-11 employees 20 10
18 JAFFN UPDATE 26-AUG-11 employees 20 10
18 JAFFN UPDATE 26-AUG-11 employees 40 10


Exception handling in Triggers







Posted by Minima-Imagem
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)