Thursday 7 April 2016

PL/SQL Triggers




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’t be local to a block


Stored Procedures vs Database Triggers


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

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

DML Trigger Components



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;

 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;

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…..]] 

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


:old and :new variables: Example


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;

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
Execute all BEFORE STATEMENT triggers
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
Execute all AFTER STATEMENT triggers

Sequence of Execution of Triggers: Example

 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;

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

Conditional Predicates: Example

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;

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
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;    
                                                    
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'

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         emp');

   COMMIT;                             -- allowed only in autonomous triggers
END; 

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

Autonomous transactions:

Autonomous transactions execute separately from the current transaction.
Unlike regular triggers, autonomous triggers can contain COMMIT and ROLLBACK statements.

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

Mutating Table Error
This error occurs when we create a row level trigger on a table that attempts to access the same table inside the trigger body                                       
A Row-level trigger can not read from or write to the table, on which it is fired. However a statement level trigger can perform these actions on the table on which it is written

Mutating Table Error: Example
   -- TRIGGER for checking that there is only 1 President in EMP

  CREATE OR REPLACE TRIGGER checjjob
   BEFORE INSERT OR UPDATE OF job
   ON emp
   FOR EACH ROW
   WHEN (UPPER (NEW.job) = 'PRESIDENT')
DECLARE
   CURSOR mycur
   IS
      SELECT empno, job
        FROM emp;
BEGIN
   FOR myvar IN mycur
   LOOP
      IF myvar.job = 'PRESIDENT'
      THEN
         raise_application_error (-20001, 'There can be only one president');
      END IF;
   END LOOP;
END;

Dealing with Mutating Table Error
Step 1: Create a package specification to contain a flag value that will be set by row level trigger and read by statement level trigger
-- package for declaring a global variable

CREATE OR REPLACE PACKAGE presidentpack
AS
   flag   NUMBER (1) := 0;
END presidentpack;

Step 2: Create an after row level trigger that sets the flag value to 1 when the new value of job is PRESIDENT

CREATE OR REPLACE TRIGGER trrowlevpresident
   AFTER INSERT OR UPDATE OF job
   ON emp
   FOR EACH ROW
   WHEN (UPPER (NEW.job) = 'PRESIDENT')
BEGIN
   presidentpack.flag := 1;
END;

Step 3: Create an after statement level trigger that performs the check.

CREATE OR REPLACE TRIGGER trstatelevpresident
   AFTER INSERT OR UPDATE OF job
   ON emp
DECLARE
   presidentcount   NUMBER (2);
BEGIN
   IF presidentpack.flag = 1
   THEN
      presidentPack.flag = 0; -- resetting the flag
      SELECT COUNT (empno)
        INTO presidentcount
        FROM emp
       WHERE UPPER (job) = 'PRESIDENT';

      IF presidentcount > 1
      THEN
         raise_application_error
              (-20001,
               'Only one
                              president is allowed'
              );
      END IF;
   END IF;
END;

Performance Impact of DML Triggers
The DML statements that initiate triggers execute slowly because they execute additional SQL statements, and the user might not know that other actions are occurring.

The execution time for a trigger event depends on the complexity of the trigger action and whether it initiates other triggers. The time increases as the number of cascaded triggers increases.


Also when a trigger is created, Oracle server has to use a background process to continuously monitor the trigger event
at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)