Triggers

Introduction

A trigger runs SQL automatically when rows are inserted, updated, or deleted—BEFORE or AFTER the event. Triggers audit changes, enforce cross-row rules, or maintain summary columns. This chapter creates simple triggers on blog_dev and notes pitfalls like recursion and hidden logic.

Prerequisites

Stored Procedures and Functions
Constraints and Keys
Tables users, posts in blog_dev

Trigger Syntax Overview

sql

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
trigger_body;

Code explanation:

FOR EACH ROW — fires once per affected row
BEFORE — can modify NEW values before write
AFTER — use when base row already stored (audit log)

Audit Log Example

Log table:

sql

CREATE TABLE post_audit (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id BIGINT UNSIGNED NOT NULL,
  action VARCHAR(10) NOT NULL,
  changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

After update on posts:

sql

DELIMITER //
 
CREATE TRIGGER tr_posts_after_update
AFTER UPDATE ON posts
FOR EACH ROW
BEGIN
  INSERT INTO post_audit (post_id, action)
  VALUES (NEW.id, 'UPDATE');
END //
 
DELIMITER ;

Test:

sql

UPDATE posts SET title = 'Updated title' WHERE id = 1;
 
SELECT * FROM post_audit;

BEFORE INSERT: Validate or Default

sql

Code explanation:

SIGNAL aborts insert with custom error—similar to CHECK but flexible
NEW — row being inserted/updated
OLD — row before update/delete (not on INSERT)

NEW and OLD

Event	NEW	OLD
INSERT	New row	—
UPDATE	New values	Previous values
DELETE	—	Deleted row

Compare columns on update:

sql

-- IF OLD.published_at IS NULL AND NEW.published_at IS NOT NULL THEN ...

Drop Trigger

sql

DROP TRIGGER IF EXISTS tr_posts_after_update;
DROP TRIGGER IF EXISTS tr_posts_before_insert;
 
SHOW TRIGGERS FROM blog_dev;

Use Cases and Warnings

Good use	Risk
Audit trail	Hidden logic hard to debug
Denormalized counter maintenance	Extra write load
Reject invalid row early	Duplicate with app validation

Warning

Avoid Trigger Chains

Trigger A updates table B whose trigger updates A—recursion or deadlocks. Prefer app layer or single clear ownership.

Triggers vs Constraints

	CHECK / FK	Trigger
Declarative	Yes	Imperative code
Portability	Better	MySQL-specific
Complex rules	Limited	Full SQL + IF

Use constraints first; triggers when constraints cannot express rule.

FAQ

One trigger per timing per table?

MySQL allows multiple—order not guaranteed between them—consolidate if dependent.

Trigger in transaction?

Part of same transaction—rollback undoes trigger effects too.

Replication?

Triggers run on primary; replicas replay row changes—not always re-fire triggers—know your topology.

Performance?

High-volume tables suffer per-row trigger cost—benchmark.

Update same table in AFTER trigger?

Possible but easy to loop—avoid updating same table that fired trigger.

ORM bypass?

Direct SQL and ORM both fire triggers—consistent.