Share this page:

Oracle/PLSQL: AFTER UPDATE Trigger

Learn how to create an AFTER UPDATE Trigger in Oracle with syntax and examples.

Description

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

Syntax

The syntax to create an AFTER UPDATE Trigger in Oracle/PLSQL 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;

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.

AFTER DELETE indicates that the trigger will fire after the UPDATE operation is executed.

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

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.

Note

Example

Let's look at an example of how to create an AFTER UPDATE 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)
);

We could then use the CREATE TRIGGER statement to 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;