tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment
Share this page:

Oracle/PLSQL: BEFORE INSERT Trigger

Learn how to create a BEFORE INSERT Trigger in Oracle with syntax and examples.

Description

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

Syntax

The syntax to create a BEFORE INSERT Trigger in Oracle/PLSQL 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;

Parameters or Arguments

OR REPLACE is optional. If specified, it allows you to re-create the trigger is it already exists so that you can change the trigger definition without issuing a DROP TRIGGER statement.

trigger_name is the name of the trigger to create.

BEFORE INSERT indicates that the trigger will fire before the INSERT operation is executed.

table_name is the name of the table that the trigger is created on.

Restrictions

  • You can not create a BEFORE trigger on a view.
  • You can update the :NEW values.
  • You can not update the :OLD values.

Note

Example

Let's look at an example of how to create an BEFORE INSERT trigger using the CREATE TRIGGER statement.

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 use the CREATE TRIGGER statement to create an 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;