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