🛑 Oracle BEFORE DELETE Trigger Tutorial

How to Use a BEFORE 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

A BEFORE DELETE trigger in Oracle executes just before a row is deleted from a table. It's typically used to archive or log the data being removed for auditing or recovery purposes.

📘 Use Case

Before an order is deleted from the orders table, we want to save the order details in the orders_audit table with information about who deleted it.

🛠️ Trigger Code

CREATE OR REPLACE TRIGGER trg_before_order_delete
BEFORE 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-DELETE',
        SYSDATE
    );
END;

This trigger runs before a row is deleted from the orders table and records the deleted data into the audit table.

🧪 How to Test the Trigger

1. Delete an order from the orders table:

DELETE FROM orders WHERE order_id = 1005;

2. Check the orders_audit table:

SELECT * FROM orders_audit WHERE order_id = 1005;

Expected Output: The deleted order appears in the audit log with a timestamp and the system user.

📌 Benefits

• Preserves deleted data for audit or backup.
• Prevents accidental data loss without a trace.
• Complies with audit and data retention policies.

Now read the full article on BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, and AFTER DELETE triggers in Oracle Database.