Common Use Cases

• Log changes to an audit table
• Enforce complex business rules (e.g., salary cannot decrease)
• Validate or transform data before it's saved
• Sync related tables automatically

Trigger Syntax (Generic)

CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
  -- SQL statements
END;

Example: Audit Inserted Users

Let's log new users into a separate audit table called UserLogs.

CREATE TABLE UserLogs (
  log_id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER,
  action TEXT,
  logged_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER log_new_user
AFTER INSERT ON Users
FOR EACH ROW
BEGIN
  INSERT INTO UserLogs (user_id, action)
  VALUES (NEW.user_id, 'User Created');
END;

Trigger Timing Options

• BEFORE INSERT – runs before a row is added
• AFTER INSERT – runs after the row is added
• BEFORE UPDATE – great for enforcing rules
• AFTER DELETE – useful for cleanup or cascade logic

Accessing Row Data in Triggers

• NEW.column_name – use this for inserted or updated values
• OLD.column_name – use this for deleted or pre-update values

Yep — your trigger can time travel. 🕰️