What are MySQL triggers

· Category: SQL & Databases

Short answer

A trigger is a database object that automatically executes a specified SQL statement before or after an INSERT, UPDATE, or DELETE event on a table.

Steps

  1. Create an audit trigger:
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (action, changed_at) VALUES ('update', NOW());
END;
  1. Use BEFORE triggers to validate or modify data.
  2. Use AFTER triggers for cascading changes and logging.
  3. Access old and new values with OLD.column and NEW.column.
  4. Drop triggers with DROP TRIGGER before_employee_update;

Tips

  • Triggers are powerful for enforcing complex constraints and audit trails.
  • They can introduce hidden logic that makes debugging difficult.

Common issues

  • Recursive triggers can cause infinite loops if not carefully controlled.
  • Triggers add overhead to every DML operation on the table.r