Chapter 9Using Database Triggers
Application Developer’s Guide, Release 7.3
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 beforeUpdateTriggers after
OperationsInsert X row/statementoperations
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
When 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
- Removing Trigger
DROP TRIGGER [schema.]trigger ;
- 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.