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
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 chages 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 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
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 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 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 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;
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>;
Ex:
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;
