Audit and Validation Triggers

Learning Focus

Use this lesson to build production-grade audit trails and complex data validation using triggers — the two most common real-world trigger patterns.

Concept Overview

Audit Triggers

An audit trigger automatically records every data change to a separate audit table. The audit table captures:

• What changed — which row, which columns
• Old values — what the data was before
• New values — what the data became
• Who changed it — the database user or application user
• When — timestamp of the change

This creates a complete change history that supports compliance, debugging, and incident investigation.

Validation Triggers

A validation trigger enforces business rules that are too complex for CHECK constraints:

• Cross-column validations ("end_date must be after start_date")
• Cross-table validations ("customer must have valid subscription")
• Conditional rules ("premium customers can order more than 100 items")

Unlike CHECK constraints, triggers can:

• Query other tables
• Use IF/CASE logic
• Return custom error messages with SIGNAL

---

Basic Syntax & Rules

Audit Table Design

A well-designed audit table captures every change with context:

CREATE TABLE orders_audit (
    audit_id     BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id     BIGINT NOT NULL,
    action       ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    changed_by   VARCHAR(100) NOT NULL,

    -- Old values (NULL for INSERT)
    old_status       VARCHAR(20),
    old_total_amount DECIMAL(12,2),

    -- New values (NULL for DELETE)
    new_status       VARCHAR(20),
    new_total_amount DECIMAL(12,2),

    changed_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_audit_order (order_id),
    INDEX idx_audit_time (changed_at)
);

Audit Trigger Template

DELIMITER //

CREATE TRIGGER trg_orders_after_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO orders_audit (
        order_id, action, changed_by,
        old_status, old_total_amount,
        new_status, new_total_amount
    ) VALUES (
        OLD.order_id, 'UPDATE', CURRENT_USER(),
        OLD.status, OLD.total_amount,
        NEW.status, NEW.total_amount
    );
END //

DELIMITER ;

Validation Trigger Template

DELIMITER //

CREATE TRIGGER trg_table_before_insert
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    IF some_condition THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Descriptive error message';
    END IF;
END //

DELIMITER ;

---

Step-by-Step Examples

Example 1: Complete Audit Trail for Orders

Step 1: Create the audit table

CREATE TABLE orders_audit (
    audit_id     BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id     BIGINT NOT NULL,
    action       ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    changed_by   VARCHAR(100) NOT NULL,
    old_status       VARCHAR(20),
    old_total_amount DECIMAL(12,2),
    new_status       VARCHAR(20),
    new_total_amount DECIMAL(12,2),
    changed_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_order (order_id),
    INDEX idx_audit_time (changed_at)
);

Step 2: Create triggers for all three events

DELIMITER //

-- After INSERT
CREATE TRIGGER trg_orders_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO orders_audit (
        order_id, action, changed_by,
        new_status, new_total_amount
    ) VALUES (
        NEW.order_id, 'INSERT', CURRENT_USER(),
        NEW.status, NEW.total_amount
    );
END //

-- After UPDATE
CREATE TRIGGER trg_orders_after_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    -- Only log if something actually changed
    IF OLD.status != NEW.status OR OLD.total_amount != NEW.total_amount THEN
        INSERT INTO orders_audit (
            order_id, action, changed_by,
            old_status, old_total_amount,
            new_status, new_total_amount
        ) VALUES (
            OLD.order_id, 'UPDATE', CURRENT_USER(),
            OLD.status, OLD.total_amount,
            NEW.status, NEW.total_amount
        );
    END IF;
END //

-- After DELETE
CREATE TRIGGER trg_orders_after_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO orders_audit (
        order_id, action, changed_by,
        old_status, old_total_amount
    ) VALUES (
        OLD.order_id, 'DELETE', CURRENT_USER(),
        OLD.status, OLD.total_amount
    );
END //

DELIMITER ;

Step 3: Test the audit trail

-- Insert an order
INSERT INTO orders (customer_id, total_amount, status)
VALUES (1, 250.00, 'pending');

-- Update it
UPDATE orders SET status = 'paid' WHERE order_id = LAST_INSERT_ID();

-- Check the audit log
SELECT * FROM orders_audit ORDER BY changed_at DESC LIMIT 5;

audit_id	order_id	action	changed_by	old_status	new_status	changed_at
2	1001	UPDATE	app_user@%	pending	paid	2026-02-10 14:31:00
1	1001	INSERT	app_user@%	NULL	pending	2026-02-10 14:30:00

---

Example 2: Cross-Column Validation

Scenario: Event start_date must be before end_date.

DELIMITER //

CREATE TRIGGER trg_events_before_insert
BEFORE INSERT ON events
FOR EACH ROW
BEGIN
    IF NEW.end_date IS NOT NULL AND NEW.start_date >= NEW.end_date THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'start_date must be before end_date';
    END IF;
END //

CREATE TRIGGER trg_events_before_update
BEFORE UPDATE ON events
FOR EACH ROW
BEGIN
    IF NEW.end_date IS NOT NULL AND NEW.start_date >= NEW.end_date THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'start_date must be before end_date';
    END IF;
END //

DELIMITER ;

INSERT INTO events (name, start_date, end_date)
VALUES ('Conference', '2026-03-15', '2026-03-10');
-- ERROR: start_date must be before end_date

---

Example 3: Cross-Table Validation

Scenario: Only customers with an active subscription can place orders.

DELIMITER //

CREATE TRIGGER trg_orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE v_sub_status VARCHAR(20);

    SELECT status INTO v_sub_status
    FROM subscriptions
    WHERE customer_id = NEW.customer_id
      AND status = 'active'
    LIMIT 1;

    IF v_sub_status IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Customer does not have an active subscription';
    END IF;
END //

DELIMITER ;

---

Example 4: Querying the Audit Trail

-- All changes to a specific order
SELECT action, old_status, new_status, changed_by, changed_at
FROM orders_audit
WHERE order_id = 1001
ORDER BY changed_at;

-- All changes by a specific user today
SELECT *
FROM orders_audit
WHERE changed_by = 'admin@%'
  AND changed_at >= CURDATE()
ORDER BY changed_at DESC;

-- Count changes per action type
SELECT action, COUNT(*) AS change_count
FROM orders_audit
WHERE changed_at >= NOW() - INTERVAL 7 DAY
GROUP BY action;

---

Practical Use Cases

1. Regulatory Compliance

Financial services, healthcare, and government systems require complete audit trails of all data changes.

2. Incident Investigation

"Who changed this order status to 'cancelled' and when?" — the audit table answers in seconds.

3. Data Recovery

Audit tables contain old values — you can reconstruct previous states without a full database restore.

4. Business Rule Enforcement

Complex rules like "maximum order frequency per customer per day" or "approval required for amounts above $10,000."

5. Change Analytics

Track how often records change, which users make the most changes, and patterns in data modifications.

---

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Audit table grows unbounded	Disk fills up over months/years	Add a retention policy: purge audit records older than N days
Auditing every column (including unchanged ones)	Noisy, hard to find real changes	Only log when values actually change (IF OLD.col != NEW.col)
No index on audit table	Querying audit history becomes slow	Add indexes on order_id and changed_at
Cross-table validation queries are slow	Every INSERT triggers a subquery	Ensure the validation query uses indexed columns
SIGNAL messages are too vague	Developers can't debug the issue	Include specific context: "Customer 1024 does not have an active subscription"
Not auditing DELETE operations	Deleted data is lost forever	Always create an AFTER DELETE audit trigger alongside INSERT and UPDATE

---

Best Practices

• Audit all three operations — INSERT, UPDATE, and DELETE on critical tables
• Only log actual changes — compare OLD and NEW values before inserting an audit row
• Index your audit tables — on the entity ID and timestamp columns
• Implement retention — schedule a job to purge old audit records (e.g., > 1 year)
• Use CURRENT_USER() — captures the database user; for application user tracking, pass it through a session variable
• Keep validation messages specific — include the failing value and the rule that was violated
• Document which tables are audited — maintain a list in your schema documentation

---

Hands-On Practice

Exercise 1: Basic Audit Trigger (Easy)

Create an audit table and trigger for a products table that tracks all price changes. Include product_id, old_price, new_price, changed_by, and changed_at. Test by updating a product's price.

Exercise 2: Date Range Validation (Medium)

Create BEFORE INSERT and BEFORE UPDATE triggers on a promotions table that validate:

1. start_date must be today or in the future
2. end_date must be after start_date
3. Duration must not exceed 90 days

Exercise 3: Full Audit System (Advanced)

Design and implement a complete audit system for an employees table:

1. Create the audit table with appropriate columns
2. Add INSERT, UPDATE, and DELETE triggers
3. Show how to query "all changes to employee #42 in the last month"
4. Add a retention query to clean up audit rows older than 1 year

---

Connection to Other Concepts

Related Concept	How It Connects
Trigger Fundamentals	This lesson builds on trigger basics with production patterns
Stored Procedures	Complex validation logic may be better in procedures than triggers
Transactions	Audit records are written within the same transaction as the original change
Indexes	Audit tables need indexes on entity IDs and timestamps
Event Scheduler	Schedule retention jobs to purge old audit records
User Management	CURRENT_USER() in triggers tracks who made changes

What to Learn Next

• 15. Event Scheduler — schedule maintenance for your audit tables

---

Visual Learning Diagram

flowchart LR
    A["Application writes\nto orders table"] --> B["AFTER INSERT/UPDATE/DELETE\ntrigger fires"]
    B --> C["Captures OLD and NEW values"]
    C --> D["Inserts into\norders_audit table"]
    D --> E["Complete change history\navailable for queries"]

    F["Compliance team"] --> E
    G["Incident investigation"] --> E
    H["Data recovery"] --> E

    classDef trigger fill:#0d6efd,stroke:#fff,color:#fff
    class B,C trigger

Quick Reference

-- Create audit trigger
DELIMITER //
CREATE TRIGGER trg_table_after_update
AFTER UPDATE ON table_name
FOR EACH ROW
BEGIN
    IF OLD.col != NEW.col THEN
        INSERT INTO table_audit (entity_id, action, old_val, new_val, changed_by, changed_at)
        VALUES (OLD.id, 'UPDATE', OLD.col, NEW.col, CURRENT_USER(), NOW());
    END IF;
END //
DELIMITER ;

-- Validation trigger
CREATE TRIGGER trg_table_before_insert
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    IF NEW.end_date <= NEW.start_date THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid date range';
    END IF;
END //

-- Query audit trail
SELECT * FROM table_audit WHERE entity_id = 1 ORDER BY changed_at;

-- View triggers on a table
SHOW TRIGGERS WHERE `Table` = 'table_name';

What's Next

• 15. Event Scheduler