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