Oracle/PLSQL: BEFORE INSERT Trigger
Blog Home



Recent Blog Posts
Show All Posts

A BEFORE INSERT Trigger means that Oracle will fire this trigger before the INSERT operation is executed.

The syntax for an BEFORE INSERT Trigger is:

CREATE or REPLACE TRIGGER trigger_name
BEFORE INSERT
    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 a BEFORE trigger on a view.
You can 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),
 create_date date,
 created_by varchar2(10)
);

We could then create a BEFORE INSERT trigger as follows:

CREATE OR REPLACE TRIGGER orders_before_insert
BEFORE INSERT
    ON orders
    FOR EACH ROW

DECLARE
    v_username varchar2(10);

BEGIN

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

    -- Update create_date field to current system date
    :new.create_date := sysdate;

    -- Update created_by field to the username of the person performing the INSERT
    :new.created_by := 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:
54.91.121.255