Oracle/PLSQL: AFTER UPDATE Trigger
Blog Home



Recent Blog Posts
Show All Posts

An AFTER UPDATE Trigger means that Oracle will fire this trigger after the UPDATE operation is executed.

The syntax for an AFTER UPDATE Trigger is:

CREATE or REPLACE TRIGGER trigger_name
AFTER UPDATE
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;

trigger_name is the name of the trigger to create.

Restrictions:

You can not create an AFTER trigger on a view.
You can not update the :NEW values.
You can not update the :OLD values.
For example:

If you had a table created as follows:

CREATE TABLE orders
( order_id number(5),
 quantity number(4),
 cost_per_item number(6,2),
 total_cost number(8,2)
);


We could then create an AFTER UPDATE trigger as follows:

CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE
    ON orders
    FOR EACH ROW

DECLARE
    v_username varchar2(10);

BEGIN

    -- Find username of person performing UPDATE into table
    SELECT user INTO v_username
    FROM dual;

    -- Insert record into audit table
    INSERT INTO orders_audit
     ( order_id,
       quantity_before,
       quantity_after,
       username )
    VALUES
     ( :new.order_id,
       :old.quantity,
       :new.quantity,
       v_username );

END; 

From www.techonthenet.com

Posted by Rajiv on Friday, February 27, 2009 at 08:31-AM under SQL Serever / Oracle / PLSQL
Post Comments (0) Back to Top  



© Copyright 2009 All rights reserved, Rajiv Sharma
Home | Contact Me | Sitemap | Privacy Notice & Disclaimer
Visitor's IP Address:
44.192.79.149