iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

Triggers

By · Reviewed & last updated · Part of MySQL Tutorial

A trigger is server-side code that fires on INSERT, UPDATE, or DELETE — BEFORE or AFTER, for each row. Use them sparingly: they hide logic from your codebase, complicate debugging, and serialise writes. They are right for audit logs, automatic timestamps, and invariants that MUST hold regardless of which client writes.

Audit log + integrity trigger + housekeeping

EXAMPLE
-- 1) Automatic 'updated_at' — sometimes the cleanest answer
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  status VARCHAR(16) NOT NULL,
  total_cents BIGINT NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- (No trigger needed — the column default + ON UPDATE handles it.)

-- 2) Append-only audit log of every change
CREATE TABLE orders_audit (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT NOT NULL,
  action  VARCHAR(8) NOT NULL,         -- INSERT/UPDATE/DELETE
  changed_by VARCHAR(64) NOT NULL,
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  before_json JSON NULL,
  after_json  JSON NULL,
  INDEX (order_id, changed_at)
);

DELIMITER //

CREATE TRIGGER trg_orders_after_ins
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO orders_audit (order_id, action, changed_by, after_json)
  VALUES (NEW.id, 'INSERT', COALESCE(@app_user, CURRENT_USER()),
          JSON_OBJECT('status', NEW.status, 'total_cents', NEW.total_cents));
END//

CREATE TRIGGER trg_orders_after_upd
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
  INSERT INTO orders_audit (order_id, action, changed_by, before_json, after_json)
  VALUES (NEW.id, 'UPDATE', COALESCE(@app_user, CURRENT_USER()),
          JSON_OBJECT('status', OLD.status, 'total_cents', OLD.total_cents),
          JSON_OBJECT('status', NEW.status, 'total_cents', NEW.total_cents));
END//

CREATE TRIGGER trg_orders_after_del
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
  INSERT INTO orders_audit (order_id, action, changed_by, before_json)
  VALUES (OLD.id, 'DELETE', COALESCE(@app_user, CURRENT_USER()),
          JSON_OBJECT('status', OLD.status, 'total_cents', OLD.total_cents));
END//

-- 3) Enforce a domain invariant the app could violate
CREATE TRIGGER trg_orders_before_upd
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
  IF NEW.status = 'shipped' AND OLD.status = 'new' THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'cannot ship a new order without paying first';
  END IF;

  IF NEW.total_cents < 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'total_cents must be >= 0';
  END IF;
END//

DELIMITER ;

-- 4) Pass identity via a SESSION VARIABLE the app sets at the start of each transaction
-- App side (PHP / PDO):
--   $pdo->beginTransaction();
--   $pdo->exec("SET @app_user = '" . $user_id . "'");
--   ... DML ...
--   $pdo->commit();

-- 5) Inspect, drop, and disable
SHOW TRIGGERS LIKE 'orders';
DROP TRIGGER IF EXISTS trg_orders_after_upd;

-- 6) Performance — triggers run inside the SAME transaction as the write
--    Heavy logic inside a trigger lengthens write latency. Keep them
--    cheap or replace with an async outbox + background processor.

Why it matters

Use triggers for invariants and audit only. Anything that involves external systems (email, queues, HTTP) belongs in the application or an outbox pattern — a trigger that calls out to a service blocks the writer until the service responds and gives you no way to retry on failure.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
DELIMITER //
CREATE TRIGGER posts_touch BEFORE UPDATE ON posts
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END//
DELIMITER ;
Try it Yourself »

Discussion

Loading…