Triggers

Why this matters

As a Data Analyst, you often work with production tables where auditability, data integrity, and up-to-date summaries matter. SQL triggers let the database automatically perform actions when rows are inserted, updated, or deleted. Common real tasks:

• Keep an audit trail of changes to important tables (orders, customers, payouts).
• Enforce simple data rules (e.g., status can only move forward).
• Maintain lightweight summary tables for fast dashboard queries.

Concept explained simply

A trigger is a small rule attached to a table (or view) that runs automatically when a specific event happens: BEFORE/AFTER an INSERT, UPDATE, or DELETE. Inside the trigger, you can read special pseudo-rows like NEW (the incoming row) and OLD (the existing row).

Mental model

Imagine a door with a sensor. Every time someone passes (row change), the sensor automatically logs the event or updates a counter. You don’t need to remember to press any button—the action just happens. That’s a trigger.

When to use triggers (and when not)

• Use: audit logs, simple integrity checks, maintaining small summary tables.
• Avoid: heavy business logic, long-running work, external API calls, complex branching—these can slow writes and confuse teammates.

Vendor notes (PostgreSQL, MySQL, SQL Server)

• PostgreSQL: Functions returning trigger; supports BEFORE/AFTER, row vs. statement triggers, OLD/NEW.
• MySQL: BEFORE/AFTER per-row triggers; use SIGNAL SQLSTATE to raise errors in validations.
• SQL Server: Has AFTER and INSTEAD OF triggers; uses pseudo-tables inserted and deleted instead of NEW/OLD.

Worked examples

Example 1: AFTER INSERT audit log

Goal: Whenever a new order is inserted, write a row into orders_audit.

-- PostgreSQL-style example
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  amount NUMERIC(12,2) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE orders_audit (
  audit_id BIGSERIAL PRIMARY KEY,
  order_id BIGINT NOT NULL,
  action TEXT NOT NULL,
  action_ts TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION trg_orders_ins_audit() RETURNS trigger AS $$
BEGIN
  INSERT INTO orders_audit(order_id, action)
  VALUES (NEW.id, 'INSERT');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_ins_audit
AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION trg_orders_ins_audit();

Try an insert, then select from orders_audit to confirm the log row appears.

Example 2: BEFORE UPDATE validation (status moves forward only)

Goal: Prevent invalid status changes (e.g., shipped → pending). Allowed flow: pending → paid → shipped.

-- PostgreSQL-style
CREATE TABLE order_status (
  id BIGSERIAL PRIMARY KEY,
  status TEXT NOT NULL CHECK (status IN ('pending','paid','shipped'))
);

CREATE OR REPLACE FUNCTION trg_order_status_validate() RETURNS trigger AS $$
DECLARE
  allowed boolean := FALSE;
BEGIN
  IF OLD.status = NEW.status THEN
    RETURN NEW;
  END IF;
  IF OLD.status = 'pending' AND NEW.status IN ('paid','shipped') THEN allowed := TRUE; END IF;
  IF OLD.status = 'paid' AND NEW.status = 'shipped' THEN allowed := TRUE; END IF;
  IF NOT allowed THEN
    RAISE EXCEPTION 'Invalid status transition: % -> %', OLD.status, NEW.status;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER order_status_validate
BEFORE UPDATE ON order_status
FOR EACH ROW EXECUTE FUNCTION trg_order_status_validate();

MySQL and SQL Server notes for this example

• MySQL: Use a BEFORE UPDATE trigger and SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...'; to block invalid transitions.
• SQL Server: Use an INSTEAD OF UPDATE trigger to validate rows in inserted/deleted, then re-apply the valid updates; or use constraints where possible.

Example 3: AFTER INSERT maintains a daily summary

Goal: Keep sales_by_day up-to-date for dashboards.

CREATE TABLE sales_by_day (
  sale_date DATE PRIMARY KEY,
  total_amount NUMERIC(12,2) NOT NULL DEFAULT 0
);

CREATE OR REPLACE FUNCTION trg_orders_insert_sum() RETURNS trigger AS $$
BEGIN
  INSERT INTO sales_by_day(sale_date, total_amount)
  VALUES (CAST(NEW.created_at AS DATE), NEW.amount)
  ON CONFLICT (sale_date)
  DO UPDATE SET total_amount = sales_by_day.total_amount + EXCLUDED.total_amount;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_insert_sum
AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION trg_orders_insert_sum();

Insert a few orders across different dates and check the totals in sales_by_day.

Hands-on: Practice Exercises

Complete the exercises below. They are also available in the Quick Test section; saving progress is available for logged-in users only. Everyone can take the test and exercises for free.

1. Exercise 1: Build an INSERT audit trigger for orders (see details in Exercises below).
2. Exercise 2: Maintain a daily sales summary with a trigger.

• I can explain the difference between BEFORE and AFTER triggers.
• I can read and use NEW and OLD values safely.
• I tested my triggers with INSERT/UPDATE/DELETE and verified results.
• I kept trigger logic short and deterministic.

Common mistakes and how to self-check

• Heavy logic in triggers: If your trigger does loops, complex queries, or calls external services, move that logic elsewhere. Keep triggers fast.
• Forgetting UPDATE and DELETE cases: If your summary only updates on INSERT, totals get out of sync. Self-check: update and delete a row; does the summary still match?
• Assuming trigger order: Multiple triggers on the same event may fire in undefined order. Prefer one trigger per event or document order if your DB supports it.
• Row vs. statement confusion: Choosing FOR EACH ROW when you meant one-time per statement can multiply effects. Self-check with multi-row inserts.
• Non-deterministic side effects: Using random values or time-sensitive logic can make debugging hard. Log clearly and keep behavior predictable.

Who this is for

• Data Analysts who work with transactional or reporting databases and need reliable audit or quick summaries.
• Analysts collaborating with engineers/DBAs to enforce lightweight data rules.

Prerequisites

• Comfort with basic SQL (SELECT, INSERT, UPDATE, DELETE).
• Understanding of primary keys, foreign keys, and constraints.
• Ability to run SQL scripts in your database environment.

Learning path

1. Review DML operations and constraints.
2. Learn trigger timing: BEFORE vs AFTER, row vs statement.
3. Write your first audit trigger on a sandbox table.
4. Add a simple validation trigger (prevent invalid status changes).
5. Maintain a small summary table (daily totals).
6. Test with edge cases: bulk inserts, updates that change dates/keys, deletes.
7. Document triggers: what they do, when they fire, and how to test them.

Practical projects

• Audit: Create <table>_audit for a key business table and log INSERT/UPDATE/DELETE with timestamps and action types.
• Summary: Build a sales_by_day or events_by_hour table maintained by triggers. Validate totals against source queries.
• Guardrail: Implement a BEFORE UPDATE trigger that blocks status regressions for a ticketing or order table. Include a clear error message.

Next steps

• Refine your audit schema to capture who made the change (if available in your DB session).
• Add UPDATE and DELETE handling for your summary to keep it fully consistent.
• Set up a small test suite: scripted inserts/updates followed by assertions on audit/summary results.

Mini challenge

Create a trigger that performs a soft delete: when a row is deleted from customers, insert that row into customers_deleted with the deletion timestamp. Keep it fast, and verify by deleting two rows and checking the archive table.

Exercises (mirror of the Exercises section below)

Exercise 1: Build an INSERT audit trigger for orders

Create tables and a trigger so each inserted order writes to orders_audit with order_id and 'INSERT'. After an insert, you should see exactly one audit row.

Exercise 2: Maintain a daily sales summary with a trigger

Create a trigger on orders that adds the inserted amount to sales_by_day for the date of created_at. Insert multiple rows across two dates and verify the sums.

Take the Quick Test

Quick Test is available to everyone for free. Log in if you want your progress to be saved.