MYSQL Tutorial

MySQL Triggers

A trigger is a named database object that activates automatically when a specified DML event (INSERT, UPDATE, DELETE) occurs on a table. Use triggers to enforce complex integrity rules, audit changes, or maintain derived data.

Trigger Timing & Events

• BEFORE vs. AFTER: Whether the trigger runs before or after the DML event.
• Supported events: INSERT, UPDATE, DELETE.

Syntax

CREATE TRIGGER trigger_name
  {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
  ON table_name
  FOR EACH ROW
BEGIN
  -- trigger logic using NEW.column or OLD.column
END;
  

Example: Audit INSERTs

CREATE TABLE user_audit (
  audit_id   INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT,
  action     VARCHAR(20),
  action_at  DATETIME
);

DELIMITER $$
CREATE TRIGGER trg_user_insert
  AFTER INSERT ON users
  FOR EACH ROW
BEGIN
  INSERT INTO user_audit (user_id, action, action_at)
  VALUES (NEW.user_id, 'INSERT', NOW());
END$$
DELIMITER ;
  

Example: Prevent Negative Stock

DELIMITER $$
CREATE TRIGGER trg_before_stock_update
  BEFORE UPDATE ON inventory
  FOR EACH ROW
BEGIN
  IF NEW.quantity < 0 THEN
    SIGNAL SQLSTATE '45000'  
      SET MESSAGE_TEXT = 'Stock cannot go negative';
  END IF;
END$$
DELIMITER ;
  

Dropping a Trigger

DROP TRIGGER IF EXISTS trg_user_insert;
  

Key Tips & Best Practices

• Use DELIMITER to wrap trigger bodies properly.
• Keep trigger logic simple—complex operations can hurt performance.
• Avoid mutating the same table in AFTER triggers to prevent recursion.
• Test triggers thoroughly to ensure they fire only when intended.
• Document triggers clearly to ease future maintenance.