PLSQL-DATABASE TRIGGERS

Triggers are similar to procedures or functions in that they are named PL/SQL blocks with declarative, executable, and exception handling sections. A trigger is executed implicitly whenever the triggering event happens. The act of executing a trigger is known as firing the trigger.

RESTRICTIONS ON TRIGGERES

Ø  Like packages, triggers must be stored as stand-alone objects in the database and cannot be local to a block or package.
Ø  A trigger does not accept arguments.

USE OF TRIGGERS          

Ø  Maintaining complex integrity constraints not possible through declarative constraints enable at table creation.
Ø  Auditing information in a table by recording the changes made and who made them.
Ø  Automatically signaling other programs that action needs to take place when chages are made to a table.
Ø  Perform validation on changes being made to tables.
Ø  Automate maintenance of the database.

TYPES OF TRIGGERS

Ø  DML Triggers
Ø  Instead of Triggers
Ø  DDL Triggers
Ø  System Triggers
Ø  Suspend Triggers




CATEGORIES

Timing            --         Before or After
Level               --         Row or Statement

Row level trigger fires once for each row affected by the triggering statement. Row level trigger is identified by the FOR EACH ROW clause.

Statement level trigger fires once either before or after the statement.

DML TRIGGER SYNTAX

Create or replace trigger <trigger_name>
{Before | after}  {insert or update or delete} on <table_name>
[For each row]
[When (…)]
[Declare]
            -- declaration
Begin
            -- trigger body
[Exception]
          -- exception section
End <trigger_name>;

DML TRIGGERS

DML trigger is fired on an INSERTUPDATE, or DELETE operation on a database table. It can be fired either before or after the statement executes, and can be fired once per affected row, or once per statement.

The combination of these factors determines the types of the triggers. These are a total of 12 possible types (3 statements * 2 timing * 2 levels).

STATEMENT LEVEL

Statement level trigger fires only once.
Ex:

SQL> create table statement_level(count varchar(50));

CREATE OR REPLACE TRIGGER STATEMENT_LEVEL_TRIGGER
      after update on student
BEGIN
      insert into statement_level values('Statement level fired');
END STATEMENT_LEVEL_TRIGGER;

Output:

SQL> update student set smarks=500;

        3 rows updated.

SQL> select * from statement_level;

COUNT
----------------------------
Statement level fired

ROW LEVEL
           
Row level trigger fires once for each row affected by the triggering statement.

Ex:

SQL> create table row_level(count varchar(50));

CREATE OR REPLACE TRIGGER ROW_LEVEL_TRIGGER
      after update on student
BEGIN
      insert into row_level values('Row level fired');
END ROW_LEVEL_TRIGGER;


Output:

SQL> update student set smarks=500;

        3 rows updated.

SQL> select * from statement_level;

COUNT
----------------------------
Row level fired
Row level fired
Row level fired

ORDER OF DML TRIGGER FIRING

Ø  Before statement level
Ø  Before row level
Ø  After row level
Ø  After statement level

Ex:
     Suppose we have a follwing table.

SQL> select * from student;

        NO NAME    MARKS
        ----- ------- ----------
         1      a         100
         2      b         200
         3      c         300
         4      d         400

SQL> create table firing_order(order varchar(50));

CREATE OR REPLACE TRIGGER BEFORE_STATEMENT
     before insert on student
BEGIN
     insert into firing_order values('Before Statement Level');
END BEFORE_STATEMENT;

CREATE OR REPLACE TRIGGER BEFORE_ROW
     before insert on student
     for each row
BEGIN
     insert into firing_order values('Before Row Level');
END BEFORE_ROW;

CREATE OR REPLACE TRIGGER AFTER_STATEMENT
    after insert on student
BEGIN
    insert into firing_order values('After Statement Level');
END AFTER_STATEMENT;

CREATE OR REPLACE TRIGGER AFTER_ROW
    after insert on student
    for each row
BEGIN
    insert into firing_order values('After Row Level');
END AFTER_ROW;

Output:
SQL> select * from firing_order;

no rows selected

SQL> insert into student values(5,'e',500);

1 row created.

SQL> select * from firing_order;



ORDER
--------------------------------------------------
Before Statement Level
Before Row Level
After Row Level
After Statement Level

SQL> select * from student;

        NO  NAME    MARKS
        ---- -------- ----------
         1       a         100
         2       b         200
         3       c         300
         4       d         400
         5       e         500

CORRELATION IDENTIFIERS IN ROW-LEVEL TRIGGERS

Inside the trigger, you can access the data in the row that is currently being processed. This is accomplished through two correlation identifiers - :old and :new.

correlation identifier is a special kind of PL/SQL bind variable. The colon in front of each indicates that they are bind variables, in the sense of host variables used in embedded PL/SQL, and indicates that they are not regularPL/SQL variables. The PL/SQL compiler will treat them as records of type

            Triggering_table%ROWTYPE.

Although syntactically they are treated as records, in reality they are not. :old and :new are also known as pseudorecords, for this reason.

TRIGGERING STATEMENT                    :OLD                                       :NEW
--------------------------------------           ----------------------------       -----------------------------------------------
INSERT                                                            all fields are NULL.      values that will be inserted
                                                                                          When the statement is completed.
UPDATE                                               original values for        new values that will be updated
                                                     the row before the       when the statement is completed.
                                                     update.
DELETE                                        original values before   all fields are NULL
                                                     the row is deleted.

Ex:
       SQL> create table marks(no number(2) old_marks number(3),new_marks
                                                 number(3));

CREATE OR REPLACE TRIGGER OLD_NEW
     before insert or update or delete on student
     for each row
BEGIN
     insert into marks values(:old.no,:old.marks,:new.marks);
END OLD_NEW;

Output:
SQL> select * from student;

        NO  NAME  MARKS
       ----- ------- ----------
         1        a         100
         2        b         200
         3        c         300
         4        d         400
         5        e         500

SQL> select * from marks;

no rows selected

SQL> insert into student values(6,'f',600);

1 row created.

SQL> select * from student;

        NO  NAME   MARKS
       ---- -------- ----------
         1       a         100
         2       b         200
         3       c         300
         4       d         400
         5       e         500
         6       f         600

SQL> select * from marks;

        NO  OLD_MARKS  NEW_MARKS
        ---- --------------- ---------------
                                            600

SQL> update student set marks=555 where no=5;

1 row updated.

SQL> select * from student;

        NO  NAME  MARKS
      ----- ------- ----------
         1       a         100
         2       b         200
         3       c         300
         4       d         400
         5       e         555
         6       f         600




SQL> select * from marks;

  NO   OLD_MARKS   NEW_MARKS
 ------ ---------------- ---------------
                                        600
      5            500              555

SQL> delete student where no = 2;

1 row deleted.

SQL> select * from student;

        NO  NAME   MARKS
       ----  -------- ----------
         1        a         100
         3        c         300
         4        d         400
         5        e         555
         6        f         600

SQL> select * from marks;

        NO  OLD_MARKS  NEW_MARKS
       -----  -------------- ----------------
                                            600
         5             500              555
         2             200

REFERENCING CLAUSE

If desired, you can use the REFERENCING clause to specify a different name for :old ane :new. This clause is found after the triggering event, before theWHEN clause.

Syntax:
        REFERENCING [old as old_name] [new as new_name]
Ex:
CREATE OR REPLACE TRIGGER REFERENCE_TRIGGER
      before insert or update or delete on student
      referencing old as old_student new as new_student
      for each row
BEGIN
      insert into marks     
               values(:old_student.no,:old_student.marks,:new_student.marks);
END REFERENCE_TRIGGER;

WHEN CLAUSE

WHEN clause is valid for row-level triggers only. If present, the trigger body will be executed only for those rows that meet the condition specified by the WHEN clause.

Syntax:
            WHEN trigger_condition;

Where trigger_condition is a Boolean expression. It will be evaluated for each row. The :new and :old records can be referenced insidetrigger_condition as well, but like REFERENCING, the colon is not used there. The colon is only valid in the trigger body.

Ex:
CREATE OR REPLACE TRIGGER WHEN_TRIGGER
      before insert or update or delete on student
      referencing old as old_student new as new_student
      for each row
      when (new_student.marks > 500)
BEGIN
      insert into marks    
               values(:old_student.no,:old_student.marks,:new_student.marks);
END WHEN_TRIGGER;

TRIGGER PREDICATES

There are three Boolean functions that you can use to determine what the operation is.
The predicates are

Ø  INSERTING
Ø  UPDATING
Ø  DELETING
Ex:

SQL> create table predicates(operation varchar(20));

CREATE OR REPLACE TRIGGER PREDICATE_TRIGGER
       before insert or update or delete on student
BEGIN
       if inserting then
          insert into predicates values('Insert');
       elsif updating then
               insert into predicates values('Update');
       elsif deleting then
               insert into predicates values('Delete');
       end if;
END PREDICATE_TRIGGER;

Output:
SQL> delete student where no=1;

1 row deleted.

SQL> select * from predicates;

MSG
---------------
Delete

SQL> insert into student values(7,'g',700);

1 row created.

SQL> select * from predicates;

MSG
---------------
Delete
Insert

SQL> update student set marks = 777 where no=7;

1 row updated.

SQL> select * from predicates;

MSG
---------------
Delete
Insert
Update

INSTEAD-OF TRIGGERS

Instead-of triggers fire instead of a DML operation. Also, instead-of triggers can be defined only on views. Instead-of triggers are used in two cases:

Ø  To allow a view that would otherwise not be modifiable to be modified.
Ø  To modify the columns of a nested table column in a view.

Ex:
SQL> create view emp_dept as select empno,ename,job,dname,loc,sal,e.deptno from
        emp e, dept d where e.deptno = d.deptno;

CREATE OR REPLACE TRIGGER INSTEAD_OF_TRIGGER
      instead of insert on emp_dept
BEGIN
      insert into dept1 values(50,'rd','bang');
      insert into   
        emp1(empno,ename,job,sal,deptno)values(2222,'saketh','doctor',8000,50);
END INSTEAD_OF_TRIGGER;
Output:

SQL>  insert into emp_dept values(2222,'saketh','doctor',8000,'rd','bang',50);
SQL> select * from emp_dept;

     EMPNO    ENAME   JOB                 SAL         DNAME           LOC            DEPTNO
    ---------- ---------- ------------    -----------   -------------  -------------   ----------
      7369     SMITH     CLERK             800        RESEARCH       DALLAS         20
      7499     ALLEN     SALESMAN     1600       SALES              CHICAGO       30
      7521     WARD     SALESMAN     1250       SALES              CHICAGO       30
      7566     JONES     MANAGER      2975       RESEARCH       DALLAS         20
      7654     MARTIN  SALESMAN     1250       SALES              CHICAGO       30
      7698     BLAKE     MANAGER      2850       SALES              CHICAGO       30
      7782     CLARK     MANAGER      2450       ACCOUNTING  NEW YORK    10
      7788     SCOTT     ANALYST       3000       RESEARCH       DALLAS          20
      7839     KING       PRESIDENT   5000       ACCOUNTING    NEW YORK   10
      7844     TURNER  SALESMAN    1500       SALES               CHICAGO       30
      7876     ADAMS    CLERK           1100       RESEARCH        DALLAS         20
      7900     JAMES     CLERK             950       SALES               CHICAGO       30
      7902     FORD       ANALYST      3000       RESEARCH         DALLAS        20
      7934     MILLER   CLERK           1300       ACCOUNTING    NEW YORK   10
      2222     saketh    doctor           8000       rd                      bang              50

SQL> select * from dept;

    DEPTNO        DNAME           LOC
   ----------    ----------------   -----------
        10         ACCOUNTING    NEW YORK
        20         RESEARCH         DALLAS
        30         SALES                CHICAGO
        40        OPERATIONS     BOSTON
        50        rd                       bang




SQL> select * from emp;

      EMPNO     ENAME       JOB              MGR     HIREDATE         SAL      COMM     DEPTNO
     ---------- ----------   ---------------  --------  --------------    ---------    ---------  ----------
      7369        SMITH     CLERK           7902       1 7-DEC-80       800                       20
      7499       ALLEN      SALESMAN    7698       20-FEB-81       1600       300         30
      7521       WARD      SALESMAN    7698       22-FEB-81       1250       500         30
      7566       JONES      MANAGER     7839       02-APR-81      2975                       20
      7654       MARTIN   SALESMAN    7698       28-SEP-81       1250     1400         30
      7698       BLAKE     MANAGER      7839       01-MAY-81     2850                       30
      7782       CLARK     MANAGER      7839       09-JUN-81      2450                       10
      7788       SCOTT     ANALYST       7566        19-APR-87     3000                       20
      7839       KING       PRESIDENT                   17-NOV-81     5000                       10
      7844       TURNER  SALESMAN    7698        08-SEP-81       1500         0           30
      7876       ADAMS    CLERK           7788        23-MAY-87     1100                       20
      7900       JAMES     CLERK           7698        03-DEC-81       950                        30
      7902       FORD     ANALYST       7566        03-DEC-81      3000                       20
      7934       MILLER   CLERK           7782        23-JAN-82      1300                      10
      2222       saketh    doctor                                                   8000                      50

DDL TRIGGERS

Oracle allows you to define triggers that will fire when Data Definition Language statements are executed.

Syntax:

Create or replace trigger <trigger_name>
{Before | after}  {DDL event} on {database | schema}
[When (…)]
[Declare]
            -- declaration
Begin
            -- trigger body
[Exception]
            -- exception section
          End <trigger_name>;

Ex:

SQL> create table my_objects(obj_name varchar(10),obj_type varchar(10),obj_owner  
        varchar(10),obj_time date);

CREATE OR REPLACE TRIGGER CREATE_TRIGGER
     after create on database
BEGIN
     insert into my_objects values(sys.dictionary_obj_name,sys.dictionary_obj_type,
                                                       sys.dictionary_obj_owner, sysdate);
END CREATE_TRIGGER;

Output:

SQL> select * from my_objects;

        no rows selected

SQL> create table stud1(no number(2));

SQL> select * from my_objects;

OBJ_NAME   OBJ_TYPE   OBJ_OWNER  OBJ_TIME
------------- --------------  --------------   ------------
STUD1            TABLE               SYS        21-JUL-07

SQL> create sequence ss;

SQL> create view stud_view as select * from stud1;

SQL> select * from my_objects;


OBJ_NAME   OBJ_TYPE     OBJ_OWNER   OBJ_TIME
--------------  -------------  ---------------- -------------
STUD1               TABLE            SYS           21-JUL-07
SS                      SEQUENCE     SYS           21-JUL-07
STUD_VIEW      VIEW             SYS           21-JUL-07

WHEN CLAUSE

If WHEN present, the trigger body will be executed only for those that meet the condition specified by the WHEN clause.

Ex:
CREATE OR REPLACE TRIGGER CREATE_TRIGGER
     after create on database
     when (sys.dictionary_obj_type = ‘TABLE’)
BEGIN
     insert into my_objects values(sys.dictionary_obj_name,sys.dictionary_obj_type,
                                                       sys.dictionary_obj_owner, sysdate);
END CREATE_TRIGGER;
Share on Google Plus

About Mindsforest

    Blogger Comment
    Facebook Comment

0 comments:

Post a Comment