How to Use an AFTER DELETE 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 DELETE trigger in Oracle fires after a row is deleted from a table. It's often used to log deletions for auditing, archiving, or recovery purposes.
📘 Use Case
When a row is deleted from the orders table, we want to log the deleted order details into the orders_audit table after the deletion has occurred.
🛠️ Trigger Code
CREATE OR REPLACE TRIGGER trg_after_order_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO orders_audit (
order_id,
customer_id,
product_id,
deleted_by,
deleted_date
) VALUES (
:OLD.order_id,
:OLD.customer_id,
:OLD.product_id,
'SYSTEM-AFTER-DELETE',
SYSDATE
);
END;
This trigger runs after the row is removed from the orders table and saves the old values in the orders_audit table for auditing.
🧪 How to Test the Trigger
- Delete an order from the table:
DELETE FROM orders WHERE order_id = 1006;
- Query the audit table:
SELECT * FROM orders_audit WHERE order_id = 1006;
Expected Output: The order that was deleted is logged in orders_audit with user and timestamp info.
📌 Benefits
- Maintains an audit trail of deleted records.
- Helps with compliance and rollback investigations.
- Supports secure and traceable data deletion practices.