Chapter
9 Using Database Triggers
Triggers
are stored procedures that are implicitly executed (fired) when a table is
updated. The contents include
·
creating, debugging, altering, dropping, enabling and
disabling triggers.
·
Sample trigger applications.
9.1
Principles of Designing Triggers
·
Design a trigger if one action will define result another
one no matter who and where the action is taking place.
·
Don’t define trigger in cases where database integrity constraints
can do the job.
·
Don’t define a recursive triggers such a trigger which is
fire by update to the table and which updates that table in the trigger’s code.
·
Trigger is complied when it is fired for the first time and
is aged out of memory. Therefore, design trigger with less than 60 lines of
code.
9.2
Types of Triggers
(1). 12 types of triggers before Oracle 8 and
14 in Oracle 8.
There are 12 triggers
Operation
Triggers before Update Triggers after
Operations Insert
X row/statement operations
Delete (or table)
3 (operation) X 2 (on rows or statement) X 2 (before and
after)
Oracle 8 will you to have INSTEAD OF row
| INSTEAD OF statement to redirect firing transaction to perform a different
action.
(2) NEW and OLD
values
The new and old values if exists can be
accessed through these two reserved words. Each represents a record. Depending
on the trigger type, you may be able to change the values in the trigger so
that the value added into a table is the value you set in the trigger.
(3) Syntax: There are difference between Oracle 8 and
Oracle 7.
Create trigger command::=
CREATE
[ OR REPLACE] TRIGGER [schema.]trigger
BEFORE
| AFTER DELETE | INSERT | UPDATE [
OF col [, col ] ]
[{ OR
DELETE | INSERT | UPDATE } ]
ON [schema.]table
[ [ REFERENCING
{ OLD [AS] old | NEW [AS] new } ]
FOR EACH ROW [ WHEN ( condition ) ] ]
·
The referencing clause rename the NEW and OLD record holders
as other names.
·
For each row define a row level trigger. Without FOR EACH
clause, you define a table level statement. WHEN condition can only be used in
row-level triggers.
(4) Semantics:
The row level trigger fired for each row
updated, delete, inserted. The table level trigger table is fired once for each
operation on the table no matter how many rows are affected.
(5) Example.
CREATE OR REPLACE TRIGGER sp_test_trigger3
After update On sp FOR EACH ROW
-- pl/sql block
DECLARE
s VARCHAR2(80);
BEGIN
s := 'New.qty: ';
if :NEW.qty IS NULL Then
s := s || 'NULL' ;
else
s := s || RPAD(:NEW.QTY, 4) ;
end if ;
s := s || ' Old.qty: ';
if :OLD.qty IS NULL Then
s := s || 'NULL' ;
else
s := s || RPAD(:OLD.QTY, 4) ;
end if ;
s := s || ' Trigger: After Update Row' ;
insert into msg values( sysdate, s);
END;
/
CREATE OR REPLACE TRIGGER test_trigger8
Before Update On sp
FOR EACH ROW WHEN (Old.qty > 50)
-- pl/sql block
DECLARE
s VARCHAR2(80);
BEGIN
s := 'New.qty: ';
if :NEW.qty IS NULL Then
s := s || 'NULL' ;
else
s := s || RPAD(:NEW.QTY, 4) ;
end if ;
s := s || ' Old.qty: ';
if :OLD.qty IS NULL Then
s := s || 'NULL' ;
else
s := s || RPAD(:OLD.QTY, 4) ;
end if ;
s := s || ' Trigger: Before Update Row' ;
insert into msg values( sysdate, s);
END;
/
-- Table level trigger. Some book call statement level trigger.
-- Fired once for the execution of statement.
-- Row level triggers fired on each row updated.
CREATE OR REPLACE TRIGGER test_trigger4
After Update On sp
-- pl/sql block
DECLARE
s VARCHAR2(80);
BEGIN
-- The table level trigger: New and OLD references are
-- not allowd
s := 'Access to New and Old not allowed in Table/Statement Trigger.' ;
insert into msg values( sysdate, s);
END;
/
When
the following statement,
Update sp set qty = qty
+ 1;
the
results from the msg table is
SQL> update sp set qty = qty + 1;
17 rows updated.
SQL> select * from msg;
DT MSG
--------- ------------------------------------------------------------
02-MAR-98 New.qty: 3 Old.qty: 2 Trigger: After Update Row
02-MAR-98 New.qty: 4 Old.qty: 3 Trigger: After Update Row
02-MAR-98 New.qty: 5 Old.qty: 4 Trigger: After Update Row
02-MAR-98 New.qty: 6 Old.qty: 5 Trigger: After Update Row
02-MAR-98 New.qty: 7 Old.qty: 6 Trigger: After Update Row
02-MAR-98 New.qty: 12 Old.qty: 11 Trigger: After Update Row
02-MAR-98 Access to New and Old not allowed in Table/Statement Trigger
02-MAR-98 New.qty: 501 Old.qty: 500 Trigger: Before Update Row
When Old.qty > 50
02-MAR-98 New.qty: 501 Old.qty: 500 Trigger: After Update Row
02-MAR-98 New.qty: 78 Old.qty: 77 Trigger: Before Update Row
When Old.qty > 50
02-MAR-98 New.qty: 78 Old.qty: 77 Trigger: After Update Row
02-MAR-98 New.qty: 301 Old.qty: 300 Trigger: Before Update Row
When Old.qty > 50
02-MAR-98 New.qty: 301 Old.qty: 300 Trigger: After Update Row
02-MAR-98 New.qty: 445 Old.qty: 444 Trigger: Before Update Row
When Old.qty > 50
02-MAR-98 New.qty: 445 Old.qty: 444 Trigger: After Update Row
02-MAR-98 New.qty: 301 Old.qty: 300 Trigger: Before Update Row
When Old.qty > 50
02-MAR-98 New.qty: 301 Old.qty: 300 Trigger: After Update Row
02-MAR-98 New.qty: 401 Old.qty: 400 Trigger: Before Update Row
When Old.qty > 50
DT MSG
--------- ------------------------------------------------------------
02-MAR-98 New.qty: 401 Old.qty: 400 Trigger: After Update Row
02-MAR-98 New.qty: 201 Old.qty: 200 Trigger: Before Update Row
W hen Old.qty > 50
02-MAR-98 New.qty: 201 Old.qty: 200 Trigger: After Update Row
02-MAR-98 New.qty: 201 Old.qty: 200 Trigger: Before Update Row
When Old.qty > 50
02-MAR-98 New.qty: 201 Old.qty: 200 Trigger: After Update Row
02-MAR-98 New.qty: 301 Old.qty: 300 Trigger: Before Update Row
When Old.qty > 50
02-MAR-98 New.qty: 301 Old.qty: 300 Trigger: After Update Row
02-MAR-98 New.qty: 401 Old.qty: 400 Trigger: Before Update Row
Whrn Old.qty > 50
02-MAR-98 New.qty: 401 Old.qty: 400 Trigger: After Update Row
02-MAR-98 New.qty: 2 Old.qty: 1 Trigger: After Update Row
28 rows selected.
SQL> spool
currently spooling to t.txt
SQL> exit
9.3
Trigger Operations
1. Removing
Trigger
DROP TRIGGER [schema.]trigger ;
2. Enabling and
Disabling Triggers
·
Enable and Disable All Triggers on a table
ALTER TABLE [schema.]table { ENABLE | DISABLE } ALL TRIGGERS ;
·
Enable and Disable Individual Trigger
ALTER TRIGGER [schema.]trigger { ENABLE | DISABLE }
9.4
Usage of Triggers
Duplicate
data, Auditing, Customizing error messages.