✏️ Oracle BEFORE UPDATE Trigger Tutorial

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

This tutorial covers Oracle BEFORE UPDATE triggers, which execute automatically before an existing row is updated. They allow you to validate or modify data before the update occurs.

How to Use a BEFORE UPDATE Trigger in Oracle

A BEFORE UPDATE trigger in Oracle executes before any update

📘 Use Case

Whenever an order's status or amount is updated in the orders table, we want to log the old details in the orders_audit table before the change occurs.

🛠️ Trigger Code

CREATE OR REPLACE TRIGGER trg_before_order_update
BEFORE UPDATE OF status, amount 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-UPDATE',
        SYSDATE
    );
END;

This trigger captures the old values before updating the status or amount fields and logs them for auditing purposes.

🧪 How to Test the Trigger

Update an order:

UPDATE orders
SET status = 'SHIPPED', amount = 1600.00
WHERE order_id = 1005;

Check the audit log:

SELECT * FROM orders_audit WHERE order_id = 1005;

Expected Result: A record is added to orders_audit showing the order’s original status and amount before the update.

📌 Benefits

Tracks changes to sensitive fields like status and amount.
Helps with rollback and audit in case of errors or disputes.
Improves data integrity and compliance.

✏️ Oracle BEFORE UPDATE Trigger Tutorial