Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Tips by Burleson

Triggers

Here is another example that maintains a history of changes to a table.  Updates and deletes are copied to a history table using a trigger This trigger takes advantage of the triggers ability to determine which event was executed.  Using this capability, the trigger can execute different sections of code based on the even that executed.  The three test conditions are INSERTING, UPDATING and DELETING.  The test will be TRUE for the event that executed the trigger.

if (updating) then… 

To see this capability in action, first create the sales_history table to hold the old rows from the sales table.  This table will have the same columns as the sales table plus a column for the change date and a column for the change method.

SQL> CREATE TABLE SALES_HISTORY
  2  ( CHG_DATE             DATE
  3  , CHG_TYPE             VARCHAR2(6)
  4  , STORE_KEY            VARCHAR2(4)
  5  , BOOK_KEY             VARCHAR2(6)
  6  , ORDER_NUMBER         VARCHAR2(20)
  7  , ORDER_DATE           DATE
  8  , QUANTITY             NUMBER(5))
  9  / 

Table created.

Now create a trigger that will capture the table changes and place the old data into the sales_history table.

SQL> create or replace trigger sales_history_tr
  2  before update or delete on sales
  3  for each row
  4  begin
  5    if (updating) then
  6      insert into sales_history
  7        ( CHG_DATE
  8        , CHG_TYPE
  9        , STORE_KEY
 10        , BOOK_KEY
 11        , ORDER_NUMBER
 12        , ORDER_DATE
 13        , QUANTITY)
 14      values
 15        ( sysdate
 16        , 'UPDATE'
 17        , :old.STORE_KEY
 18        , :old.BOOK_KEY
 19        , :old.ORDER_NUMBER
 20        , :old.ORDER_DATE
 21        , :old.QUANTITY);
 22    else
 23       insert into sales_history
 24        ( CHG_DATE
 25        , CHG_TYPE
 26        , STORE_KEY
 27        , BOOK_KEY
 28        , ORDER_NUMBER
 29        , ORDER_DATE
 30        , QUANTITY)
 31      values
 32        ( sysdate
 33        , 'DELETE'
 34        , :old.STORE_KEY
 35        , :old.BOOK_KEY
 36        , :old.ORDER_NUMBER
 37        , :old.ORDER_DATE
 38        , :old.QUANTITY);
 39    end if;
 40  end; /

The above example uses the :OLD specification to capture the values in the row before it is changed and save them to the sales_history table, along with the change date/time and the change type (update or delete).  Notice the condition test at line 5 to determine if the event was an update or a delete.

Sometimes the :OLD and :NEW reference is confusing.  You can redefine these names using the referencing clause.  The example below replaces the standard notation with early and late

SQL> create or replace trigger sales_history2_tr
  2  before update or delete on sales
  3  referencing old as early new as late
  4  for each row
  5  begin
  6      insert into sales_history
  7        ( CHG_DATE,
  8        CHG_TYPE,
  9        STORE_KEY,
 10        BOOK_KEY,
 11        ORDER_NUMBER,
 12        ORDER_DATE,
 13        QUANTITY)
 14      values
 15        ( sysdate,
 16        'UPDATE',
 17        :early.STORE_KEY,
 18        :early.BOOK_KEY,
 19        :early.ORDER_NUMBER,
 20        :early.ORDER_DATE,
 21        :early.QUANTITY);
 22  end;
 23  / 

Trigger created.

Often the default values are defined in the referencing clause for clarity. 

create or replace trigger sales_history2_tr
before update or delete on sales
referencing old as old new as new
for each row
begin
  …

A trigger  does not need to perform an action on the object that fired the event.  Below is a trigger that fires after an insert on a table and executes a procedure in a package.

SQL> create or replace trigger do_something_tr
  2  after insert on book_author
  3  begin
  4     ref_cursor_pak.sales_row_count;
  5  end;
  6  / 

Trigger created.

This trigger  executes the procedure sales_row_count from the ref_cursor_pak example after each insert into the book_author tables.

SQL> insert into book_author values
  2    ('A101','B110',.15);
Number of rows: 0
First Order Number: O101
First Order Number: O102
Number of rows: 2
Cursor Closed 

1 row created.

In the example, it appears that the trigger fired before the row was created.  That is not the case.  The results appear that way because of the manner that SQL*Plus retrieved the results from the buffer.  Because the trigger executes after the insert event, if the insert fails, the trigger will not fire.

SQL> insert into book_author values
  2    ('A101','B110');
insert into book_author values
            *
ERROR at line 1:
ORA-00947: not enough values

Notice that the trigger did not fire because the row insertion failed.


The above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3   

John Garmany 

http://www.rampant-books.com/book_2005_1_easy_plsql.htm


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.