Trigger Use Cases and Limitations

Where triggers earn their place

-- A rule a CHECK constraint cannot express: it reads another table
DELIMITER $$

CREATE TRIGGER trg_assignments_before_insert
BEFORE INSERT ON assignments
FOR EACH ROW
BEGIN
    DECLARE v_project_ended DATE;

    SELECT end_date INTO v_project_ended
    FROM   projects WHERE id = NEW.project_id;

    IF v_project_ended IS NOT NULL AND v_project_ended < CURDATE() THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Cannot assign an employee to a finished project';
    END IF;
END$$

DELIMITER ;

The costs, stated plainly

• Invisible. An INSERT does more than the statement says. A developer reading application code has no clue a trigger exists, and debugging starts in the wrong place.
• Per row cost. FOR EACH ROW means a 100,000 row update runs the trigger 100,000 times. Bulk loads slow to a crawl.
• Transaction scope. The trigger runs inside the caller's transaction and extends how long its locks are held.
• Cascade risk. A trigger writing to another table can fire that table's triggers, and the chain is easy to lose track of.
• Hard to test. Triggers rarely appear in the application test suite, so a change to one is validated in production.
• Error surfacing. A SIGNAL from a trigger reaches the application as a database error, not a friendly validation message.

What triggers do not fire on

This list is why an audit trigger is a strong control but not a complete one: someone with TRUNCATE privilege leaves no audit rows at all.

Alternatives worth preferring

-- A generated column does what a BEFORE trigger often gets used for,
-- but declaratively - and it can be indexed.
ALTER TABLE order_items
  ADD COLUMN line_total DECIMAL(12,2)
      AS (quantity * unit_price) STORED;

Generated columns are available in MySQL 5.7+, MariaDB 5.2+, PostgreSQL 12+, SQL Server (computed columns) and Oracle. Where one fits, it beats a trigger on every axis: visible in the schema, no per row code, and indexable.

Important rules

• A trigger cannot commit or roll back; it lives inside the caller's transaction.
• MySQL forbids a trigger from modifying the table it is defined on.
• An error raised in a trigger aborts the whole statement.
• Trigger execution order between multiple triggers on the same event is undefined unless declared.
• Triggers are per table and per row; there is no statement level trigger in MySQL.

Common mistakes

• Implementing core business logic in triggers, then being unable to explain a bug.
• Relying on triggers for audit while leaving TRUNCATE privilege granted.
• Heavy triggers on tables that receive bulk loads.
• Chained triggers across three tables that nobody can trace.
• Using a trigger where a DEFAULT, a CHECK or a generated column would do.

Best practices

• Use triggers for things that must be true regardless of who writes - audit and integrity - not for application behaviour.
• Exhaust the declarative options first: constraints, defaults, generated columns.
• Keep bodies tiny and avoid writing to tables that themselves have triggers.
• Document every trigger next to the table definition, and include them in migration reviews.
• Test the bulk path: measure a large insert with and without the trigger before deploying it.

Practice

1. Name two rules in the sample schema that a CHECK cannot enforce but a trigger can.
2. Replace a BEFORE INSERT trigger that computes line_total with a generated column.
3. Explain why an audit trigger alone does not guarantee a complete audit trail.