How to Use an AFTER UPDATE Trigger in Oracle
Before studying and practicing with all types of triggers, you must first create a scenario. Below are example schemas to follow before returning here to continue. Link: Click Here
An AFTER UPDATE trigger in Oracle executes after a row has been updated. It’s commonly used to log the final (new) values into audit tables or sync data to other systems.
📘 Use Case
Whenever any update happens on the orders table, we want to record the new values in the orders_audit table.
🛠️ Trigger Code
CREATE OR REPLACE TRIGGER trg_after_order_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
INSERT INTO orders_audit (
order_id,
customer_id,
product_id,
deleted_by,
deleted_date
) VALUES (
:NEW.order_id,
:NEW.customer_id,
:NEW.product_id,
'SYSTEM-AFTER-UPDATE',
SYSDATE
);
END;
This trigger captures the NEW values (i.e., the updated data) and logs it to the audit table after the update has occurred.
🧪 How to Test the Trigger
- Update an existing order:
UPDATE orders SET status = 'DELIVERED', amount = 1700.00 WHERE order_id = 1005;
- Verify the audit log:
SELECT * FROM orders_audit WHERE order_id = 1005;
Expected Output: A new record is added to orders_audit showing the final order values after the update.
📌 Benefits
- Helps track all updates for auditing or reporting.
- Ensures accountability for data changes.
- Provides an audit trail for updated business transactions.