Oracle/PLSQL: AFTER DELETE Trigger
Blog Home



Recent Blog Posts
Show All Posts

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

The syntax for an AFTER DELETE Trigger is:

CREATE or REPLACE TRIGGER trigger_name
AFTER DELETE
    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 DELETE UPDATE trigger as follows:

CREATE OR REPLACE TRIGGER orders_after_delete
AFTER DELETE
    ON orders
    FOR EACH ROW

DECLARE
    v_username varchar2(10);

BEGIN

    -- Find username of person performing the DELETE on the table
    SELECT user INTO v_username
    FROM dual;

    -- Insert record into audit table
    INSERT INTO orders_audit
     ( order_id,
       quantity,
       cost_per_item,
       total_cost,
       delete_date,
       deleted_by)
    VALUES
     ( :old.order_id,
       :old.quantity,
       :old.cost_per_item,
       :old.total_cost,
       sysdate,
       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