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          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.

 

 

(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 [ 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.

 

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