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 on insert or update or delete
[For
each row]
Begin
--
trigger body
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).
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
Also
we have triggering_firing_order table with firing_order as the field.
CREATE
OR REPLACE TRIGGER TRIGGER1
before
insert on student
BEGIN
insert
into trigger_firing_order values('Before Statement Level');
END
TRIGGER1;
CREATE
OR REPLACE TRIGGER TRIGGER2
before
insert on student
for
each row
BEGIN
insert
into trigger_firing_order values('Before Row Level');
END
TRIGGER2;
CREATE
OR REPLACE TRIGGER TRIGGER3
after
insert on student
BEGIN
insert
into trigger_firing_order values('After Statement Level');
END
TRIGGER3;
CREATE
OR REPLACE TRIGGER TRIGGER4
after
insert on student
for
each row
BEGIN
insert
into trigger_firing_order values('After Row Level');
END
TRIGGER4;
Output:
SQL>
select * from trigger_firing_order;
no
rows selected
SQL>
insert into student values(5,'e',500);
1
row created.
SQL>
select * from trigger_firing_order;
FIRING_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:
Suppose we
have a table called marks with fields no, old_marks, new_marks.
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:
CREATE
OR REPLACE TRIGGER PREDICATE_TRIGGER
before
insert or update or delete on student
BEGIN
if
inserting then
insert
into predicates values('I');
elsif
updating then
insert
into predicates values('U');
elsif
deleting then
insert
into predicates values('D');
end
if;
END
PREDICATE_TRIGGER;
Output:
SQL>
delete student where no=1;
1
row deleted.
SQL>
select * from predicates;
MSG
---------------
D
SQL>
insert into student values(7,'g',700);
1
row created.
SQL>
select * from predicates;
MSG
---------------
D
I
SQL>
update student set marks = 777 where no=7;
1
row updated.
SQL>
select * from predicates;
MSG
---------------
D
I
U
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:
