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