Chapter 9†††††††† Using 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 before†††††††††††††† Update††††††††††††††††††††††††††††††††††††† Triggers after

††††††††††† Operations†††††††††††††††††† Insert††† ††††† Xrow/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.

 

 

(2)     Syntax:There are difference between Oracle 8 and Oracle 7.

 

Create trigger command::=

 

†† CREATE [ OR REPLACE] TRIGGER[schema.]trigger

†† BEFORE | AFTER††† DELETE | INSERT | UPDATE [ OFcol [, 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.

 

(3)     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.

 

(4)     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.