Oracle Triggers Explained: What They Are and When to Actually Use One

We named one of these back when we talked about naming conventions (trg_employees_bi), but never actually built one. A trigger is a block of PL/SQL that Oracle runs automatically whenever something happens to a table: a row gets inserted, updated, or deleted. You don't call it directly. It just fires on its own when the event it's watching for occurs.

1. What a trigger actually does

Think of a trigger as a rule attached to a table: "whenever X happens, do Y first." That could mean validating data before it's saved, keeping an audit log of changes, or enforcing a business rule that a plain constraint can't express. The trigger runs as part of the same transaction as the statement that fired it, so if the trigger fails, the whole operation rolls back.

2. The two things that define a trigger

Every trigger is defined by when it fires and how often.

Timing: BEFORE, AFTER, or INSTEAD OF

• BEFORE: runs before the change is written. Use this to validate or modify the incoming data.
• AFTER: runs once the change has already been written. Use this for logging or triggering side effects that depend on the row already existing.
• INSTEAD OF: only used on views. It replaces the operation entirely, which matters because some views can't be updated directly.

Level: row or statement

• FOR EACH ROW: fires once per affected row. If you update 200 rows in one statement, this trigger runs 200 times. This is what lets you reference :NEW and :OLD for that specific row.
• Statement-level (the default when you leave off FOR EACH ROW): fires once for the whole statement, no matter how many rows it touched.

Combine timing and level and you get the usual suspects: BEFORE INSERT, AFTER UPDATE, BEFORE DELETE FOR EACH ROW, and so on.

3. Naming them so they're easy to find later

Following the convention from last time, a trigger name should tell you the table and the timing at a glance:

Suffix	Meaning	Example
bi	Before insert	trg_employees_bi
bu	Before update	trg_employees_bu
au	After update	trg_employees_au
ad	After delete	trg_employees_ad

4. Example: stopping a pay cut before it happens

Say the rule at your company is that salaries can only go up, never down, through the app. A BEFORE UPDATE row-level trigger can enforce that at the database level, so it holds even if someone tries to update the table directly:

SQL

CREATE OR REPLACE TRIGGER trg_employees_bu
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF :NEW.empl_salary < :OLD.empl_salary THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be decreased.');
    END IF;
END;
/

Try to update a row with a lower salary and Oracle stops the whole statement before it commits, with the message you defined right there in the error.

5. Example: keeping an audit trail

A more common use case is logging changes after they happen, so you have a history of what changed and when. First, a table to hold the log:

SQL

CREATE TABLE employees_audit (
    audit_id       NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    empl_id        NUMBER,
    old_salary     NUMBER(10,2),
    new_salary     NUMBER(10,2),
    changed_on     DATE DEFAULT SYSDATE
);

Then an AFTER UPDATE trigger to fill it in whenever a salary actually changes:

SQL

CREATE OR REPLACE TRIGGER trg_employees_au
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (NEW.empl_salary IS NOT NULL)
BEGIN
    IF :NEW.empl_salary != :OLD.empl_salary THEN
        INSERT INTO employees_audit (empl_id, old_salary, new_salary)
        VALUES (:OLD.empl_id, :OLD.empl_salary, :NEW.empl_salary);
    END IF;
END;
/

Now every salary change quietly leaves a paper trail, without a single line of extra code in the application itself.

6. When triggers help, and when they get in the way

GOOD FIT Rules that must hold no matter which app or script touches the table. Audit logging that shouldn't depend on the application remembering to write it. Small, fast validations that don't call out to anything else.

POOR FIT Complex business logic that's easier to read and test in the application layer. Anything slow, since a trigger runs inside the caller's transaction and can hold up every insert or update. Logic that surprises other developers who don't know a trigger exists. Undocumented triggers are notorious for causing confusing bugs.

---

Summary

• A trigger is PL/SQL that runs automatically on insert, update, or delete. You never call it yourself.
• BEFORE triggers can validate or change incoming data. AFTER triggers react once the change is already saved.
• Use triggers for rules and audit logs that need to hold no matter what touches the table, and keep everything else in the application.