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
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
Ø 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.
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
Ø 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;
No comments:
Post a Comment