Triggers

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

What Is a Trigger?

A trigger is a PL/SQL block that Oracle fires automatically in response to a specific event on a table or view. You do not call a trigger — it runs on its own whenever the event it is watching occurs. Triggers are used to enforce business rules that constraints alone cannot handle, maintain audit trails, synchronise related tables, and prevent invalid operations.

Every trigger is defined by three things: the event that fires it (INSERT, UPDATE, or DELETE), the timing (BEFORE the change is applied or AFTER it has been applied), and the level (once per statement or once per affected row). Getting these three choices right is the foundation of writing effective triggers.

• Triggers fire automatically — no explicit call is needed
• BEFORE triggers run before the DML change — useful for validation and modifying incoming values
• AFTER triggers run after the DML change — useful for auditing and cascading updates to other tables
• Statement-level triggers fire once per DML statement regardless of how many rows are affected
• Row-level triggers fire once per affected row — marked with FOR EACH ROW
• Row-level triggers use :NEW and :OLD to access the new and previous column values

BEFORE Row Trigger — Validation and Defaulting

A BEFORE row trigger runs before each row is written to the table. This makes it ideal for two things: validating the incoming data and raising an error before a bad value is stored, and defaulting column values that cannot be handled by a simple DEFAULT constraint — such as values derived from other columns or from a sequence.

-- BEFORE INSERT OR UPDATE trigger on products.
-- Validates that price is positive and defaults the created_date if not provided.
CREATE OR REPLACE TRIGGER dataplexa_store.trg_products_before_iud
BEFORE INSERT OR UPDATE ON dataplexa_store.products
FOR EACH ROW                          -- fires once per affected row
BEGIN
    -- Validation: reject a zero or negative price
    IF :NEW.price <= 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Price must be greater than zero.');
    END IF;
    -- Default: set created_date on INSERT if not supplied
    IF INSERTING AND :NEW.created_date IS NULL THEN
        :NEW.created_date := SYSDATE;
    END IF;
    -- Audit: record who last modified the row
    :NEW.last_modified_by   := SYS_CONTEXT('USERENV', 'SESSION_USER');
    :NEW.last_modified_date := SYSDATE;
END trg_products_before_iud;
/
-- Test the validation
INSERT INTO dataplexa_store.products (product_id, product_name, price)
VALUES (99, 'Test Product', -10);

• :NEW.column_name refers to the value being written — in a BEFORE trigger you can modify :NEW values before they reach the table
• :OLD.column_name refers to the value that existed before the change — only meaningful in UPDATE and DELETE triggers
• RAISE_APPLICATION_ERROR(-20001, 'message') raises a user-defined error — error numbers must be between -20000 and -20999
• INSERTING, UPDATING, and DELETING are predicates available inside triggers that fire on multiple events — they return TRUE for the event currently executing
• SYS_CONTEXT('USERENV', 'SESSION_USER') returns the Oracle username of the current session

AFTER Row Trigger — Audit Trail

An AFTER row trigger runs after each row has been successfully written. Because the change is already committed to the row at this point, AFTER triggers are the right place for audit logging — recording what changed, when, and who made the change. The trigger writes to a separate audit table, so the history is preserved even if the original row is later deleted.

-- Audit table that records every price change on the products table
CREATE TABLE dataplexa_store.product_price_audit (
    audit_id        NUMBER          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id      NUMBER          NOT NULL,
    old_price       NUMBER(10,2),
    new_price       NUMBER(10,2),
    changed_by      VARCHAR2(100),
    changed_at      DATE            DEFAULT SYSDATE
);
-- AFTER UPDATE trigger -- fires only when the price column is updated
CREATE OR REPLACE TRIGGER dataplexa_store.trg_product_price_audit
AFTER UPDATE OF price ON dataplexa_store.products
FOR EACH ROW
WHEN (OLD.price != NEW.price)         -- only log rows where price actually changed
BEGIN
    INSERT INTO dataplexa_store.product_price_audit
        (product_id, old_price, new_price, changed_by)
    VALUES
        (:OLD.product_id, :OLD.price, :NEW.price,
         SYS_CONTEXT('USERENV', 'SESSION_USER'));
END trg_product_price_audit;
/
-- Trigger fires automatically when this UPDATE runs
UPDATE dataplexa_store.products
SET    price = 319.99
WHERE  product_id = 7;

• AFTER UPDATE OF price narrows the trigger to fire only when the price column specifically is updated — other UPDATE statements on the table do not fire it
• The WHEN clause adds a row-level condition — the trigger body only executes if the condition is true; note that :NEW and :OLD are written without the colon inside the WHEN clause
• GENERATED ALWAYS AS IDENTITY is Oracle 12c+ syntax for an auto-incrementing primary key column
• Audit triggers should never fail silently — if the INSERT into the audit table fails it will roll back the original UPDATE too

Statement-Level Trigger

A statement-level trigger fires once per DML statement, regardless of how many rows the statement affects. It has no access to :NEW or :OLD because it does not operate at the row level. Statement-level triggers are useful for enforcing time-window restrictions — preventing modifications outside business hours — or logging that a DML operation occurred without caring about individual row values.

-- Statement-level trigger: prevent DELETE on orders outside business hours.
-- Fires once per DELETE statement -- no FOR EACH ROW clause.
CREATE OR REPLACE TRIGGER dataplexa_store.trg_orders_no_weekend_delete
BEFORE DELETE ON dataplexa_store.orders
BEGIN
    -- TO_CHAR(SYSDATE, 'DY') returns the abbreviated day name: MON, TUE ... SAT, SUN
    IF TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
        RAISE_APPLICATION_ERROR(-20002,
            'Orders cannot be deleted on weekends.');
    END IF;
END trg_orders_no_weekend_delete;
/
-- Attempting a DELETE on a Saturday raises the error
DELETE FROM dataplexa_store.orders WHERE order_id = 1001;

• No FOR EACH ROW clause — this is a statement-level trigger and fires exactly once however many rows the DELETE affects
• TO_CHAR(SYSDATE, 'DY') returns a three-character day abbreviation in the database's language setting — use TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') for reliable cross-language behaviour
• Statement-level triggers cannot access :NEW or :OLD — attempting to do so raises a compilation error

Managing Triggers

Triggers can be enabled and disabled without being dropped. Disabling a trigger leaves its definition intact but stops it from firing — useful during bulk data loads where trigger overhead would be prohibitive. Use USER_TRIGGERS to list all triggers in the current schema along with their status, event, and the table they watch.

-- Disable a trigger temporarily (e.g. during a bulk load)
ALTER TRIGGER dataplexa_store.trg_product_price_audit DISABLE;
-- Re-enable it after the load completes
ALTER TRIGGER dataplexa_store.trg_product_price_audit ENABLE;
-- Disable ALL triggers on a table at once
ALTER TABLE dataplexa_store.products DISABLE ALL TRIGGERS;
-- Re-enable all triggers on the table
ALTER TABLE dataplexa_store.products ENABLE ALL TRIGGERS;
-- Drop a trigger permanently
DROP TRIGGER dataplexa_store.trg_orders_no_weekend_delete;
-- List all triggers in the current schema
SELECT trigger_name, trigger_type, triggering_event,
       table_name, status
FROM   user_triggers
ORDER  BY table_name, trigger_name;

• DISABLE ALL TRIGGERS on a table is the standard approach before a large bulk INSERT or data migration — re-enable immediately after
• A disabled trigger still exists and shows in USER_TRIGGERS with STATUS = DISABLED
• Dropping a table automatically drops all triggers defined on it
• Triggers that raise unhandled exceptions roll back the entire DML statement that fired them — not just the trigger body

Lesson Summary

Practice Questions

Quiz

Next up — Performance Optimization Basics — How Oracle's query optimiser works, how to read an execution plan, and the most effective techniques for speeding up slow queries.