How to Use an AFTER INSERT 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 INSERT trigger in Oracle is used to perform actions after a new row is successfully inserted into a table. It's often used for auditing, logging, or syncing data.
📘 Use Case
When a new order is placed in the orders table, we want to automatically insert a record in the orders_audit table with audit details.
🛠️ Trigger Code
CREATE OR REPLACE TRIGGER trg_after_order_insert
AFTER INSERT 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',
SYSDATE
);
END;
This trigger runs after a new order is inserted and logs the order into the audit table with default values for deleted_by and deleted_date.
🧪 How to Test the Trigger
- Insert a new order:
INSERT INTO orders (order_id, customer_id, product_id, amount, status) VALUES (1005, 1, 101, 1500.00, 'NEW');
- Check the audit log:
SELECT * FROM orders_audit WHERE order_id = 1005;
Expected Result: The inserted order appears in the audit table with system-generated metadata.
📌 Benefits
- Ensures every order is audited automatically.
- No manual logging required by the application.
- Useful for compliance and data recovery purposes.