DATABASE TRIGGERS

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 TRIGGERS

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

USE OF TRIGGERS

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

TYPES OF TRIGGERS

  1. DML Triggers
  2. Instead of Triggers
  3. DDL Triggers
  4. System Triggers
  5. 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
A DML trigger is fired on an INSERT, UPDATE, or DELETES 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

  1. Before statement level
  2. Before row level
  3. After row level
  4. 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.

A 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 regular PL/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 the new values that will be updated when
row before the update the statement is completed.
—————————————————————————————————————————-
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 the WHEN 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 inside trigger_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
     1 INSERTING
     2 UPDATING
     3 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:
1 To allow a view that would otherwise not be modifiable to be modified.
2 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    17-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;

 SYSTEM TRIGGERS

System triggers will fire whenever database-wide event occurs. The following are the database event triggers. To create system trigger you need ADMINISTER DATABASE TRIGGER privilege.

  1.   STARTUP
  2.   SHUTDOWN
  3.   LOGON
  4.   LOGOFF
  5.   SERVERERROR

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

SQL> create table user_logs(u_name varchar(10),log_time timestamp);

CREATE OR REPLACE TRIGGER AFTER_LOGON
     after logon on database
BEGIN
     insert into user_logs values(user,current_timestamp);
END AFTER_LOGON;
Output:
SQL> select * from user_logs;
        no rows selected

SQL> conn saketh/saketh
SQL> select * from user_logs;
U_NAME     LOG_TIME
---------- ------------------------------------------------
SAKETH     22-JUL-07 12.07.13.140000 AM

SQL> conn system/oracle
SQL> select * from user_logs;
U_NAME     LOG_TIME
---------- ------------------------------------------------
SAKETH     22-JUL-07 12.07.13.140000 AM
SYSTEM     22-JUL-07 12.07.34.218000 AM

SQL> conn scott/tiger
SQL> select * from user_logs;
U_NAME     LOG_TIME
---------- -----------------------------------------------
SAKETH     22-JUL-07 12.07.13.140000 AM
SYSTEM     22-JUL-07 12.07.34.218000 AM
SCOTT      22-JUL-07 12.08.43.093000 AM

 SERVERERROR

The SERVERERROR event can be used to track errors that occur in the database. The error code is available inside the trigger through the SERVER_ERROR attribute function.

Ex:
SQL> create table my_errors(error_msg varchar(200));

CREATE OR REPLACE TRIGGER SERVER_ERROR_TRIGGER
        after servererror on database
BEGIN
        insert into my_errors values(dbms_utility.format_error_stack);
END SERVER_ERROR_TRIGGER;
Output:
SQL> create table ss (no));
create table ss (no))
                    *
ERROR at line 1:
ORA-00922: missing or invalid option

SQL> select * from my_errors;
ERROR_MSG
-------------------------------------------------------------
ORA-00922: missing or invalid option

SQL> insert into student values(1,2,3);
insert into student values(1,2,3)
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from my_errors;
ERROR_MSG
-------------------------------------------------------------
ORA-00922: missing or invalid option
ORA-00942: table or view does not exist

 SERVER_ERROR ATTRIBUTE FUNCTION

It takes a single number type of argument and returns the error at the position on the error stack indicated by the argument. The position 1 is the top of the stack.

Ex:
CREATE OR REPLACE TRIGGER SERVER_ERROR_TRIGGER
        after servererror on database
BEGIN
        insert into my_errors values(server_error(1));
END SERVER_ERROR_TRIGGER;

 SUSPEND TRIGGERS

This will fire whenever a statement is suspended. This might occur as the result of a space issue such as exceeding an allocated tablepace quota. This functionality can be used to address the problem and allow the operatin to continue.

Syntax:

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

Ex:
    SQL> create tablespace my_space datafile 'f:\my_file.dbf' size 2m;
    SQL> create table student(sno number(2),sname varchar(10)) tablespace my_space;

CREATE OR REPLACE TRIGGER SUSPEND_TRIGGER
      after suspend on database
BEGIN
      dbms_output.put_line(‘ No room to insert in your tablespace');
END SUSPEND_TRIGGER;
Output:
Insert more rows in student table then , you will get No room to insert in your tablespace

 AUTONOMOUS TRANSACTION

Prior to Oracle8i, there was no way in which some SQL operations within a transaction could be committed independent of the rest of the operations. Oracle allows this, however, through autonomous transactions. An autonomous transaction is a transaction that is started within the context of another transaction, known as parent transaction, but is independent of it. The autonomous transaction can be committed or rolled back regardless ot the state of the parent transaction.

Ex:
CREATE OR REPLACE TRIGGER AUTONOMOUS_TRANSACTION_TRIGGER
after insert on student
DECLARE
pragma autonomous_transaction;
BEGIN
update student set marks = 555;
commit;
END AUTONOMOUS_TRANSACTION_TRIGGER;
Output:
SQL> select * from student;
        NO  NA      MARKS
       ----- ----- -- ----------
         1    a         111
         2    b         222
         3    c         300

SQL>  insert into student values(4,'d',444);

SQL> select * from student;
        NO   NA         MARKS
        ---- ------  ----------
         1    a         555
         2    b         555
         3    c         555
         4    d         444

 RESTRICTIONS ON AUTONOMOUS TRANSACTION

  1. If an autonomous transaction attempts to access a resource held by the main transaction, a deadlock can occur in you program.
  2. You cannot mark all programs in a package as autonomous with a single PRAGMA declaration. You must indicate autonomous transactions explicity in each program.
  3. To exit without errors from an autonomous transaction program that has executed at least one INSERT or UPDATE or DELETE, you must perform an explicit commit or rollback.
  4. The COMMIT and ROLLBACK statements end the active autonomous transaction, but they do not force the termination of the autonomous routine. You can have multiple COMMIT and/or ROLLBACK statements inside your autonomous block.
  5. You can not rollback to a savepoint set in the main transaction.
  6. The TRANSACTIONS parameter in the oracle initialization file specifies the maximum number of transactions allowed concurrently in a session. The default value is 75 for this, but you can increase the limit.

MUTATING TABLES
There are restrictions on the tables and columns that a trigger body may access. In order to define these restrictions, it is necessary to understand mutating and constraining tables.
A mutating table is table that is currentlty being modified by a DML statement and the trigger event also DML statement. A mutating table error occurs when a row-level trigger tries to examine or change a table that is already undergoing change.
A constraining table is a table that might need to be read from for a referential integrity constraint.

Ex:
CREATE OR REPLACE TRIGGER MUTATING_TRIGGER
before delete on student
for each row

DECLARE
ct number;
BEGIN
select count(*) into ct from student where no = :old.no;
END MUTATING_TRIGGER;
Output:
SQL> delete student where no = 1;
delete student where no = 1
       *
ERROR at line 1:
ORA-04091: table SCOTT.STUDENT is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.T", line 4
ORA-04088: error during execution of trigger 'SCOTT.T'

 HOW TO AVOID MUTATING TABLE ERROR ?

  • By using autonomous transaction
  • By using statement level trigger
  

Related posts:

Leave a Reply