Using BEFORE UPDATE Trigger in Oracle

This Oracle tutorial explains how to create a BEFORE UPDATE Trigger in Oracle with syntax and examples.

Description

A BEFORE UPDATE Trigger skill that Oracle will fireplace this set off earlier than the UPDATE operation is executed.

Syntax

The syntax to create a BEFORE UPDATE Trigger in Oracle/PLSQL is:

CREATE [ OR REPLACE ] TRIGGER trigger_name
BEFORE UPDATE
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   -- variable declarations

BEGIN
   -- trigger code

EXCEPTION
   WHEN ...
   -- exception handling

END;

Parameters or Arguments

OR REPLACE Optional. If specified, it approves you to re-create the trigger is it already exists so that you can trade the set off definition except issuing a DROP TRIGGER statement. trigger_name The identify of the trigger to create. BEFORE UPDATE It suggests that the trigger will fireplace before the UPDATE operation is executed. table_name The identify of the desk that the trigger is created on.

Restrictions

You can now not create a BEFORE trigger on a view. You can replace the :NEW values. You can no longer update the :OLD values.

Note

See also how to create AFTER DELETE, AFTER INSERT, AFTER UPDATE, BEFORE DELETE, and BEFORE INSERT triggers. See also how to drop a trigger.

Example

Let’s look at an example of how to create an BEFORE UPDATE set off the usage of 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),
  updated_date date,
  updated_by varchar2(10)
);

We ought to then use the CREATE TRIGGER announcement to create an BEFORE UPDATE trigger as follows:

TIP: When the usage of SQLPlus, you need to enter lower on a new line after the trigger. Otherwise, the script won’t execute.

CREATE OR REPLACE TRIGGER orders_before_update
BEFORE UPDATE
   ON orders
   FOR EACH ROW

DECLARE
   v_username varchar2(10);

BEGIN

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

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

   -- Update updated_by field to the username of the person performing the UPDATE
   :new.updated_by := v_username;

END;

/