Oracle AFTER UPDATE trigger.

Preamble

In this tutorial you will learn how to create an AFTER UPDATE trigger (after updating) in Oracle with syntax and examples.

Oracle executes (excites) the AFTER UPDATE trigger after running the UPDATE operator.

AFTER UPDATE trigger syntax

CREATE [ OR REPLACE ] TRIGGER name_trigger
AFTER UPDATE
ON table_name
[ FOR EACH ROW ]
DECLARE
- variable declaration
BEGIN
- trigger code
EXCEPTION
WHEN ...
- emergency handling
END;

Parameters or arguments

• Trigger_name – The name of the trigger to be created.
• AFTER UPDATE – indicates that the trigger is triggered after the UPDATE operator is executed.
• table_name – The name of the table for which the trigger was created.

Restrictions

• You cannot create a trigger in views.
• You cannot update :NEW (new) values.
• You cannot update :OLD (old) values.

Example

Let’s take an example of how to create an AFTER UPDATE trigger using the CREATE TRIGGER construction.
If you have created a table of the following structure:

CREATE TABLE orders
(order_id number(5),
number(4),
cost_per_item number(6,2),
total_cost number(8,2)
);

Then we will create the 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 the person username running UPDATE in the table
SELECT user INTO v_username
FROM dual;
-- Insert a row in the audit table
INSERT INTO orders_audit
( order_id,
quantity_before,
quantity_after,
username )
VALUES
( :new.order_id,
:old.quantity,
:new.quantity,
v_username );
END;

About Enteros

​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​