Trigger Fundamentals

Learning Focus

Use this lesson to understand triggers — database-level event handlers that execute automatically when data changes, without the application needing to know about them.

Concept Overview

What Is a Trigger?

A trigger is a stored SQL routine that fires automatically when a specific data-modifying event (INSERT, UPDATE, or DELETE) occurs on a table. Unlike procedures (which you call explicitly), triggers execute without any application code awareness.

-- Every time a row is inserted into orders,
-- this trigger automatically fires:
CREATE TRIGGER trg_orders_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- automatic actions here
END;

Why Use Triggers?

Use Case	How Triggers Help
Audit logging	Automatically record who changed what and when
Data validation	Reject invalid data before it's written
Derived columns	Auto-compute values (e.g., set updated_at)
Maintaining aggregates	Update summary tables when detail rows change
Enforcing complex rules	Business rules that CHECK constraints can't express

The Two Dimensions of Triggers

Every trigger has two properties:

Dimension	Options	Meaning
Timing	BEFORE / AFTER	Fire before or after the data change
Event	INSERT / UPDATE / DELETE	Which operation activates the trigger

This gives you six possible trigger types:

Trigger Type	When to Use
BEFORE INSERT	Validate or transform data before it's saved
AFTER INSERT	Log the insertion, update aggregates
BEFORE UPDATE	Validate new values, auto-set updated_at
AFTER UPDATE	Log changes, sync related tables
BEFORE DELETE	Archive the row before deletion
AFTER DELETE	Update aggregates, log the deletion

Context Keywords: NEW and OLD

Inside a trigger, you access the affected row's data with:

Keyword	Available In	Contains
NEW	INSERT, UPDATE	The new values being written
OLD	UPDATE, DELETE	The existing values before the change

-- In an UPDATE trigger:
-- OLD.price = the current price (before change)
-- NEW.price = the new price (being set)

---

Basic Syntax & Rules

Creating a Trigger

DELIMITER //

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- trigger body
END //

DELIMITER ;

Naming Convention

A clear naming pattern helps you manage triggers:

trg_{table}_{timing}_{event}

Examples:

• trg_orders_after_insert
• trg_users_before_update
• trg_products_before_delete

Rules and Limitations

Rule	Details
One trigger per timing+event combination	You can't have two BEFORE INSERT triggers on the same table
No recursive triggers	A trigger on table A cannot fire a trigger on table A again
FOR EACH ROW	MySQL only supports row-level triggers (fires once per affected row)
No transactions inside triggers	You can't use START TRANSACTION or COMMIT inside a trigger
Performance impact	Triggers add overhead to every write operation

---

Step-by-Step Examples

Example 1: Auto-Set updated_at on Every UPDATE

Step 1: Create the trigger

DELIMITER //

CREATE TRIGGER trg_orders_before_update
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END //

DELIMITER ;

Step 2: Test it

-- Check current value
SELECT order_id, status, updated_at FROM orders WHERE order_id = 1001;
-- updated_at: 2026-02-08 09:15:00

-- Update the order
UPDATE orders SET status = 'shipped' WHERE order_id = 1001;

-- Check again
SELECT order_id, status, updated_at FROM orders WHERE order_id = 1001;
-- updated_at: 2026-02-10 14:30:00 (automatically updated!)

---

Example 2: BEFORE INSERT Validation

Scenario: Reject orders with negative amounts.

DELIMITER //

CREATE TRIGGER trg_orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    IF NEW.total_amount < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Order amount cannot be negative';
    END IF;

    IF NEW.total_amount = 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Order amount cannot be zero';
    END IF;
END //

DELIMITER ;

INSERT INTO orders (customer_id, total_amount) VALUES (1, -50.00);
-- ERROR: Order amount cannot be negative

INSERT INTO orders (customer_id, total_amount) VALUES (1, 250.00);
-- Success!

---

Example 3: BEFORE DELETE — Archive Before Removing

DELIMITER //

CREATE TRIGGER trg_orders_before_delete
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO orders_archive (
        order_id, customer_id, total_amount,
        status, order_date, archived_at
    )
    VALUES (
        OLD.order_id, OLD.customer_id, OLD.total_amount,
        OLD.status, OLD.order_date, NOW()
    );
END //

DELIMITER ;

Now every deleted order is automatically preserved in orders_archive before removal.

---

Example 4: Maintaining a Running Total

DELIMITER //

CREATE TRIGGER trg_order_items_after_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders
    SET total_amount = total_amount + (NEW.quantity * NEW.unit_price)
    WHERE order_id = NEW.order_id;
END //

DELIMITER ;

Every time an item is added to an order, the order's total is automatically updated.

---

Practical Use Cases

1. Audit Trails

Record every change to critical tables — who changed what, when, old values, new values.

2. Data Validation

Business rules too complex for CHECK constraints (cross-column, cross-table validation).

3. Auto-Computed Values

Automatically set created_at, updated_at, or derive values from other columns.

4. Aggregate Maintenance

Keep summary/counter tables in sync with detail tables without manual updates.

5. Data Archival

Automatically copy rows to archive tables before deletion.

---

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Expensive operations inside triggers	Every INSERT/UPDATE/DELETE becomes slow	Keep trigger logic minimal; offload heavy work to async processes
Hidden business logic	Developers don't know about triggers; unexpected behavior	Document triggers prominently; use clear naming conventions
Cascading trigger chains	Trigger on table A updates table B, whose trigger updates table C...	Minimize cross-table trigger chains; prefer application-level orchestration
No trigger documentation	Team doesn't know triggers exist on a table	List triggers in your schema documentation
Forgetting that triggers fire per row	Updating 10,000 rows fires the trigger 10,000 times	Design trigger logic to be lightweight; consider batch alternatives
Not testing triggers	Trigger bugs only discovered in production	Write explicit test cases that INSERT/UPDATE/DELETE and verify trigger behavior

---

Best Practices

• Keep triggers lightweight — minimize logic; one or two operations max
• Name clearly — trg_{table}_{timing}_{event} makes it obvious what fires when
• Document triggers — add them to your schema documentation and README
• Don't hide business logic — triggers should augment, not replace, application logic
• Test trigger behavior — verify with actual INSERT/UPDATE/DELETE operations
• Monitor performance — compare write latency before and after adding triggers

---

Hands-On Practice

Exercise 1: Auto-Timestamp (Easy)

Create a BEFORE INSERT trigger on a comments table that automatically sets created_at = NOW().

Exercise 2: Stock Validation (Medium)

Create a BEFORE INSERT trigger on order_items that:

1. Checks if NEW.quantity exceeds the product's stock_quantity
2. If so, raises an error with SIGNAL
3. If valid, reduces stock_quantity by NEW.quantity

Exercise 3: Change Log (Advanced)

Create an AFTER UPDATE trigger on products that records every price change in a price_changes table with columns: product_id, old_price, new_price, changed_at. Only log when the price actually changes.

---

Connection to Other Concepts

Related Concept	How It Connects
Stored Procedures	Procedures are called explicitly; triggers fire automatically
Control Flow	Triggers use the same IF/CASE syntax
SIGNAL	Used inside triggers to reject invalid data
Constraints	CHECK handles simple rules; triggers handle complex cross-column logic
Event Scheduler	Events are time-based; triggers are event-based
Audit Logging	Triggers are the foundation of automatic audit trails

What to Learn Next

• Audit and Validation Triggers — detailed patterns for audit trails

---

Visual Learning Diagram

flowchart TD
    A["INSERT / UPDATE / DELETE\non table"] --> B{"BEFORE trigger exists?"}
    B -->|Yes| C["Execute BEFORE trigger"]
    C -->|SIGNAL error| D["❌ Operation rejected"]
    C -->|OK| E["Execute the actual operation"]
    B -->|No| E
    E --> F{"AFTER trigger exists?"}
    F -->|Yes| G["Execute AFTER trigger"]
    F -->|No| H["✅ Operation complete"]
    G --> H

    classDef error fill:#dc3545,stroke:#fff,color:#fff
    classDef success fill:#28a745,stroke:#fff,color:#fff
    class D error
    class H success

Quick Reference

-- Create trigger
DELIMITER //
CREATE TRIGGER trg_name BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN ... END //
DELIMITER ;

-- Access row data
NEW.column_name  -- new values (INSERT, UPDATE)
OLD.column_name  -- old values (UPDATE, DELETE)

-- Reject operation
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Reason';

-- View triggers
SHOW TRIGGERS FROM database_name;
SHOW CREATE TRIGGER trg_name\G

-- Drop trigger
DROP TRIGGER IF EXISTS trg_name;

What's Next

• Audit and Validation Triggers