Loading learning content...
Consider two seemingly similar requirements:
Validate and modify salary data before it's stored — Ensure salaries fall within departmental ranges, and if below minimum, automatically adjust to minimum.
Log salary changes after they're committed — Record the old and new values in an audit table for compliance tracking.
Both involve reacting to salary updates, but they require fundamentally different trigger timing. The first must happen before the data reaches storage (so we can validate and modify). The second must happen after constraints pass (so we only log valid changes).
Trigger activation timing—BEFORE, AFTER, or INSTEAD OF—isn't just syntax; it determines what you can do, what you can see, and when side effects occur. Choosing wrong timing leads to triggers that fail mysteriously, modify the wrong data, or violate transactional semantics.
By the end of this page, you will understand the precise semantics of BEFORE, AFTER, and INSTEAD OF triggers. You'll know when each timing is appropriate, how to access and modify row data, and how timing interacts with constraints and transactions. We'll also address vendor-specific behaviors and common pitfalls.
BEFORE triggers fire after the DML statement is parsed but before the actual modification is applied to the table. This timing provides unique capabilities:
Key Characteristics of BEFORE Triggers:
Primary Use Cases:
12345678910111213141516171819202122232425262728293031323334353637383940
-- BEFORE INSERT Trigger: Data cleansing and derived values-- PostgreSQL syntax CREATE OR REPLACE FUNCTION clean_employee_data()RETURNS TRIGGER AS $$BEGIN -- Data cleansing: normalize email to lowercase NEW.email := LOWER(TRIM(NEW.email)); -- Data cleansing: proper case for names NEW.first_name := INITCAP(TRIM(NEW.first_name)); NEW.last_name := INITCAP(TRIM(NEW.last_name)); -- Derived value: set creation timestamp NEW.created_at := CURRENT_TIMESTAMP; NEW.updated_at := CURRENT_TIMESTAMP; -- Derived value: generate employee code NEW.employee_code := 'EMP-' || TO_CHAR(CURRENT_DATE, 'YYYY') || '-' || NEXTVAL('employee_seq'); -- Validation: ensure salary is within department range IF NEW.salary < (SELECT min_salary FROM departments WHERE dept_id = NEW.dept_id) THEN -- Option 1: Adjust to minimum NEW.salary := (SELECT min_salary FROM departments WHERE dept_id = NEW.dept_id); -- Option 2: Reject (uncomment to use instead) -- RAISE EXCEPTION 'Salary % below minimum for department %', -- NEW.salary, NEW.dept_id; END IF; -- IMPORTANT: Must return NEW for the insert to proceed RETURN NEW;END;$$ LANGUAGE plpgsql; CREATE TRIGGER trg_clean_employee_before_insert BEFORE INSERT ON employees FOR EACH ROW EXECUTE FUNCTION clean_employee_data();In PostgreSQL, a BEFORE row-level trigger function MUST return the row to be inserted/updated (typically NEW, possibly modified), or NULL to cancel the operation silently. Returning NULL does NOT raise an error—it simply skips the row. Failing to return anything is an error. Other DBMS have different semantics—Oracle triggers don't require explicit returns.
12345678910111213141516171819202122232425262728293031323334353637383940414243
-- BEFORE UPDATE Trigger: Complex business rule validation-- PostgreSQL syntax CREATE OR REPLACE FUNCTION validate_salary_change()RETURNS TRIGGER AS $$DECLARE max_increase_pct CONSTANT DECIMAL := 0.25; -- 25% max increase change_pct DECIMAL;BEGIN -- Calculate percentage change IF OLD.salary > 0 THEN change_pct := (NEW.salary - OLD.salary) / OLD.salary; ELSE change_pct := 0; END IF; -- Rule 1: Salary increases over 25% require VP approval flag IF change_pct > max_increase_pct AND NOT NEW.vp_approved THEN RAISE EXCEPTION 'Salary increase of %.1f%% exceeds 25%% limit. VP approval required.', change_pct * 100 USING HINT = 'Set vp_approved = TRUE after obtaining approval'; END IF; -- Rule 2: Cannot decrease salary of employees with > 10 years tenure IF NEW.salary < OLD.salary AND (CURRENT_DATE - OLD.hire_date) > INTERVAL '10 years' THEN RAISE EXCEPTION 'Cannot decrease salary of employees with 10+ years tenure' USING DETAIL = 'Employee % has been with company since %', OLD.employee_id, OLD.hire_date; END IF; -- Rule 3: Update the modification timestamp NEW.updated_at := CURRENT_TIMESTAMP; NEW.updated_by := CURRENT_USER; RETURN NEW;END;$$ LANGUAGE plpgsql; CREATE TRIGGER trg_validate_salary_change BEFORE UPDATE OF salary ON employees FOR EACH ROW EXECUTE FUNCTION validate_salary_change();BEFORE DELETE Triggers:
BEFORE DELETE triggers are less common than BEFORE INSERT/UPDATE because deletion rarely requires modification. However, they're valuable for:
123456789101112131415161718192021222324252627
-- BEFORE DELETE Trigger: Implement soft delete pattern-- PostgreSQL syntax CREATE OR REPLACE FUNCTION soft_delete_customer()RETURNS TRIGGER AS $$BEGIN -- Instead of deleting, mark as inactive UPDATE customers SET is_active = FALSE, deleted_at = CURRENT_TIMESTAMP, deleted_by = CURRENT_USER WHERE customer_id = OLD.customer_id; -- Return NULL to cancel the actual DELETE -- The row remains, but is now marked as deleted RETURN NULL;END;$$ LANGUAGE plpgsql; CREATE TRIGGER trg_soft_delete_customer BEFORE DELETE ON customers FOR EACH ROW EXECUTE FUNCTION soft_delete_customer(); -- Note: Applications should filter WHERE is_active = TRUE-- To permanently delete, use: ALTER TABLE customers DISABLE TRIGGER trg_soft_delete_customer;AFTER triggers fire after the DML modification has been applied and all constraints have been validated. At this point, the data change is complete (though not yet committed to disk), and the trigger responds to the fait accompli.
Key Characteristics of AFTER Triggers:
Primary Use Cases:
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293
-- AFTER Trigger: Comprehensive audit logging-- PostgreSQL syntax -- First, create the audit tableCREATE TABLE employee_audit ( audit_id SERIAL PRIMARY KEY, operation VARCHAR(10) NOT NULL, table_name VARCHAR(64) NOT NULL, record_id INTEGER NOT NULL, old_values JSONB, new_values JSONB, changed_columns TEXT[], changed_by VARCHAR(128) DEFAULT CURRENT_USER, changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, session_ip INET, application_name VARCHAR(128)); -- The trigger functionCREATE OR REPLACE FUNCTION audit_employee_changes()RETURNS TRIGGER AS $$DECLARE v_old JSONB; v_new JSONB; v_changed TEXT[]; v_column TEXT;BEGIN -- For INSERT: only NEW values IF TG_OP = 'INSERT' THEN v_new := TO_JSONB(NEW); INSERT INTO employee_audit (operation, table_name, record_id, new_values, session_ip, application_name) VALUES ('INSERT', TG_TABLE_NAME, NEW.employee_id, v_new, INET_CLIENT_ADDR(), CURRENT_SETTING('application_name', TRUE)); RETURN NEW; -- For UPDATE: both OLD and NEW, plus list of changed columns ELSIF TG_OP = 'UPDATE' THEN v_old := TO_JSONB(OLD); v_new := TO_JSONB(NEW); -- Identify which columns changed v_changed := ARRAY( SELECT key FROM JSONB_EACH(v_old) AS o(key, value) WHERE v_new->key IS DISTINCT FROM o.value ); -- Only log if something actually changed IF ARRAY_LENGTH(v_changed, 1) > 0 THEN INSERT INTO employee_audit (operation, table_name, record_id, old_values, new_values, changed_columns, session_ip, application_name) VALUES ('UPDATE', TG_TABLE_NAME, NEW.employee_id, v_old, v_new, v_changed, INET_CLIENT_ADDR(), CURRENT_SETTING('application_name', TRUE)); END IF; RETURN NEW; -- For DELETE: only OLD values ELSIF TG_OP = 'DELETE' THEN v_old := TO_JSONB(OLD); INSERT INTO employee_audit (operation, table_name, record_id, old_values, session_ip, application_name) VALUES ('DELETE', TG_TABLE_NAME, OLD.employee_id, v_old, INET_CLIENT_ADDR(), CURRENT_SETTING('application_name', TRUE)); RETURN OLD; END IF; RETURN NULL;END;$$ LANGUAGE plpgsql; -- Create triggers for each operation (or use a combined trigger)CREATE TRIGGER trg_audit_employee_insert AFTER INSERT ON employees FOR EACH ROW EXECUTE FUNCTION audit_employee_changes(); CREATE TRIGGER trg_audit_employee_update AFTER UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION audit_employee_changes(); CREATE TRIGGER trg_audit_employee_delete AFTER DELETE ON employees FOR EACH ROW EXECUTE FUNCTION audit_employee_changes();The TG_OP variable (PostgreSQL) tells you which operation triggered the function. This allows a single function to handle INSERT, UPDATE, and DELETE with appropriate logic for each. Oracle uses INSERTING, UPDATING, DELETING predicates similarly. This pattern reduces code duplication.
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374
-- AFTER Trigger: Cascading update to maintain derived data-- PostgreSQL syntax -- Scenario: order_items table with orders having a cached totalCREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INTEGER NOT NULL, order_total DECIMAL(12,2) DEFAULT 0, item_count INTEGER DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP); CREATE TABLE order_items ( item_id SERIAL PRIMARY KEY, order_id INTEGER REFERENCES orders(order_id), product_id INTEGER NOT NULL, quantity INTEGER NOT NULL, unit_price DECIMAL(10,2) NOT NULL, line_total DECIMAL(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED); -- Trigger function to maintain order totalsCREATE OR REPLACE FUNCTION update_order_totals()RETURNS TRIGGER AS $$DECLARE v_order_id INTEGER;BEGIN -- Determine which order to update IF TG_OP = 'DELETE' THEN v_order_id := OLD.order_id; ELSE v_order_id := NEW.order_id; END IF; -- Recalculate totals from source (ensures accuracy) UPDATE orders SET order_total = COALESCE(( SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = v_order_id ), 0), item_count = ( SELECT COUNT(*) FROM order_items WHERE order_id = v_order_id ) WHERE order_id = v_order_id; -- For UPDATE that changes order_id, also update the old order IF TG_OP = 'UPDATE' AND OLD.order_id <> NEW.order_id THEN UPDATE orders SET order_total = COALESCE(( SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = OLD.order_id ), 0), item_count = ( SELECT COUNT(*) FROM order_items WHERE order_id = OLD.order_id ) WHERE order_id = OLD.order_id; END IF; RETURN COALESCE(NEW, OLD);END;$$ LANGUAGE plpgsql; CREATE TRIGGER trg_update_order_totals AFTER INSERT OR UPDATE OR DELETE ON order_items FOR EACH ROW EXECUTE FUNCTION update_order_totals();INSTEAD OF triggers replace the triggering DML operation entirely. Rather than the operation firing before/after the trigger, the operation is replaced by the trigger's actions. The original DML never executes.
Purpose and Rationale:
Views are often read-only when they involve:
INSTEAD OF triggers make such views updatable by providing explicit logic to translate the view-level DML into base table operations.
Key Characteristics:
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115
-- INSTEAD OF Trigger: Make a join view updatable-- PostgreSQL syntax -- Base tablesCREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, customer_name VARCHAR(100) NOT NULL, contact_email VARCHAR(255)); CREATE TABLE customer_addresses ( address_id SERIAL PRIMARY KEY, customer_id INTEGER REFERENCES customers(customer_id) ON DELETE CASCADE, address_type VARCHAR(20) DEFAULT 'billing', street VARCHAR(255), city VARCHAR(100), state VARCHAR(50), postal_code VARCHAR(20), is_primary BOOLEAN DEFAULT FALSE); -- View that joins customers with their primary addressCREATE OR REPLACE VIEW customer_with_address ASSELECT c.customer_id, c.customer_name, c.contact_email, ca.address_id, ca.street, ca.city, ca.state, ca.postal_codeFROM customers cLEFT JOIN customer_addresses ca ON c.customer_id = ca.customer_id AND ca.is_primary = TRUE; -- INSTEAD OF INSERT: Create customer and optionally primary addressCREATE OR REPLACE FUNCTION insert_customer_with_address()RETURNS TRIGGER AS $$DECLARE v_customer_id INTEGER;BEGIN -- Insert the customer INSERT INTO customers (customer_name, contact_email) VALUES (NEW.customer_name, NEW.contact_email) RETURNING customer_id INTO v_customer_id; -- If address info provided, insert primary address IF NEW.street IS NOT NULL OR NEW.city IS NOT NULL THEN INSERT INTO customer_addresses (customer_id, street, city, state, postal_code, is_primary) VALUES (v_customer_id, NEW.street, NEW.city, NEW.state, NEW.postal_code, TRUE); END IF; RETURN NEW;END;$$ LANGUAGE plpgsql; CREATE TRIGGER trg_insert_customer_with_address INSTEAD OF INSERT ON customer_with_address FOR EACH ROW EXECUTE FUNCTION insert_customer_with_address(); -- INSTEAD OF UPDATE: Update appropriate base table(s)CREATE OR REPLACE FUNCTION update_customer_with_address()RETURNS TRIGGER AS $$BEGIN -- Update customer data UPDATE customers SET customer_name = NEW.customer_name, contact_email = NEW.contact_email WHERE customer_id = OLD.customer_id; -- Update or insert address IF OLD.address_id IS NOT NULL THEN -- Update existing address UPDATE customer_addresses SET street = NEW.street, city = NEW.city, state = NEW.state, postal_code = NEW.postal_code WHERE address_id = OLD.address_id; ELSIF NEW.street IS NOT NULL OR NEW.city IS NOT NULL THEN -- Create new primary address INSERT INTO customer_addresses (customer_id, street, city, state, postal_code, is_primary) VALUES (OLD.customer_id, NEW.street, NEW.city, NEW.state, NEW.postal_code, TRUE); END IF; RETURN NEW;END;$$ LANGUAGE plpgsql; CREATE TRIGGER trg_update_customer_with_address INSTEAD OF UPDATE ON customer_with_address FOR EACH ROW EXECUTE FUNCTION update_customer_with_address(); -- INSTEAD OF DELETE: Delete customer (address cascades)CREATE OR REPLACE FUNCTION delete_customer_with_address()RETURNS TRIGGER AS $$BEGIN DELETE FROM customers WHERE customer_id = OLD.customer_id; RETURN OLD;END;$$ LANGUAGE plpgsql; CREATE TRIGGER trg_delete_customer_with_address INSTEAD OF DELETE ON customer_with_address FOR EACH ROW EXECUTE FUNCTION delete_customer_with_address();PostgreSQL supports INSTEAD OF triggers only on views (not tables). Oracle and SQL Server support INSTEAD OF on views. SQL Server also allows INSTEAD OF triggers on tables with certain restrictions. Always verify support for your target DBMS.
Selecting the correct trigger timing is crucial. Here's a comprehensive comparison to guide your decisions:
| Characteristic | BEFORE | AFTER | INSTEAD OF |
|---|---|---|---|
| When it fires | Before DML, before constraints | After DML, after constraints | Replaces DML entirely |
| Can modify NEW? | Yes | No (row already stored) | Yes (defines what happens) |
| Constraint status | Constraints not yet checked | Constraints passed | N/A (no automatic DML) |
| Generated values | May not be available | Fully available | Depends on trigger logic |
| Can cancel operation? | Yes (return NULL or raise) | No (already done) | Yes (don't do anything) |
| Typical use cases | Validation, cleansing, computed values | Audit, cascading, notifications | Updatable views |
| Performance impact | Directly on write path | Directly on write path | Replaces write entirely |
| Supported on | Tables (usually) | Tables (usually) | Views (primarily) |
If you use an AFTER trigger for validation and raise an error, the DML has already been written (but not committed). Rolling back works, but you've wasted I/O. Use BEFORE for validation to reject before writes occur. Use AFTER only for logging/cascading where you need the final values.
When multiple triggers exist for the same event and timing on a table, execution order becomes critical. Different DBMS handle this differently:
PostgreSQL:
01_validate, 02_transform, 03_audit) to control orderOracle:
FOLLOWS and PRECEDES clauses to specify explicit orderingCREATE TRIGGER trg_a ... BEFORE INSERT ... FOLLOWS trg_bSQL Server:
FIRST or LAST using sp_settriggerorderMySQL:
FOLLOWS and PRECEDES clauses1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162
-- PostgreSQL: Control order via naming-- Triggers fire alphabetically CREATE TRIGGER "01_validate_employee" BEFORE INSERT ON employees FOR EACH ROW EXECUTE FUNCTION validate_employee_data(); CREATE TRIGGER "02_transform_employee" BEFORE INSERT ON employees FOR EACH ROW EXECUTE FUNCTION transform_employee_data(); CREATE TRIGGER "03_generate_code" BEFORE INSERT ON employees FOR EACH ROW EXECUTE FUNCTION generate_employee_code(); -- Oracle: Explicit orderingCREATE OR REPLACE TRIGGER validate_employee_trg BEFORE INSERT ON employees FOR EACH ROWBEGIN -- Validation logic NULL;END;/ CREATE OR REPLACE TRIGGER transform_employee_trg BEFORE INSERT ON employees FOR EACH ROW FOLLOWS validate_employee_trg -- Explicit orderingBEGIN -- Transformation logic NULL;END;/ -- SQL Server: Using sp_settriggerorderCREATE TRIGGER validate_employee_trg ON employeesAFTER INSERTASBEGIN -- Validation logic PRINT 'Validating';END;GO CREATE TRIGGER audit_employee_trg ON employeesAFTER INSERTASBEGIN -- Audit logic PRINT 'Auditing';END;GO -- Set audit to fire lastEXEC sp_settriggerorder @triggername = 'audit_employee_trg', @order = 'Last', @stmttype = 'INSERT';Complete Execution Sequence:
For a single DML statement affecting multiple rows, the full sequence is:
Note: If any step fails, the entire statement rolls back (including prior rows' changes).
If your triggers rely on a specific execution order to function correctly, this is often a design smell. Each trigger should ideally operate independently. If order matters, consolidate into a single trigger with clearly sequenced steps, or use explicit ordering mechanisms.
While the conceptual model of BEFORE/AFTER triggers is consistent, implementation details vary significantly. Here's a comparison of major DBMS:
1234567891011121314151617181920212223242526272829
-- PostgreSQL uses trigger functions-- Function defined separately from trigger CREATE OR REPLACE FUNCTION employee_trigger_fn()RETURNS TRIGGER AS $$BEGIN IF TG_OP = 'INSERT' THEN NEW.created_at := CURRENT_TIMESTAMP; RETURN NEW; ELSIF TG_OP = 'UPDATE' THEN NEW.updated_at := CURRENT_TIMESTAMP; -- OLD is read-only IF OLD.salary <> NEW.salary THEN RAISE NOTICE 'Salary changed from % to %', OLD.salary, NEW.salary; END IF; RETURN NEW; ELSIF TG_OP = 'DELETE' THEN RETURN OLD; END IF;END;$$ LANGUAGE plpgsql; -- Trigger references the functionCREATE TRIGGER trg_employee_changes BEFORE INSERT OR UPDATE OR DELETE ON employees FOR EACH ROW EXECUTE FUNCTION employee_trigger_fn(); -- Special variables: TG_OP, TG_TABLE_NAME, TG_WHEN, TG_LEVELExperience reveals recurring patterns and mistakes in trigger timing. Here are the most important:
When debugging timing issues, add logging to both BEFORE and AFTER versions of your trigger and observe the values. You'll often find that the data you expected isn't available, or modifications you made were lost. This empirical approach catches assumptions faster than reasoning alone.
Trigger timing is the foundation of effective trigger design. Let's consolidate the key insights:
What's Next:
With timing mastered, the next page explores INSERT, UPDATE, and DELETE triggers in depth—examining how the triggering event type affects available data, typical use cases, and advanced patterns for each operation type.
You now understand the precise semantics of BEFORE, AFTER, and INSTEAD OF triggers across major database systems. This knowledge ensures you choose the right timing for each use case and avoid common pitfalls that derail trigger implementations.