Trigger best practices and limitations in MySQL

Introduction

Triggers help automate actions in the database when data changes, but using them carefully avoids problems.

Automatically update a timestamp when a record changes.

Keep audit logs of data changes without changing application code.

Enforce complex rules that cannot be done with simple constraints.

Prevent invalid data changes by checking conditions before insert or update.

Syntax

MySQL

CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE
ON table_name
FOR EACH ROW
BEGIN
  -- trigger logic here
END

Triggers run automatically when the specified event happens on the table.

You can choose BEFORE or AFTER the event to run the trigger.

Examples

This trigger updates the last_modified column before any update on employees.

MySQL

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  SET NEW.last_modified = NOW();
END

This trigger stops updates that try to set a negative salary.

MySQL

CREATE TRIGGER prevent_negative_salary
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be negative';
  END IF;
END

Sample Program

This example creates a table and a trigger that updates last_modified automatically when salary changes.

MySQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  salary DECIMAL(10,2),
  last_modified TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER update_last_modified
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  SET NEW.last_modified = NOW();
END$$
DELIMITER ;

INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 5000);
UPDATE employees SET salary = 5500 WHERE id = 1;
SELECT id, name, salary, last_modified FROM employees WHERE id = 1;

Important Notes

Triggers can slow down database operations if they have complex logic.

Avoid recursive triggers that call themselves, it can cause errors.

Not all errors inside triggers are easy to debug; test carefully.

Summary

Triggers automate actions on data changes without extra application code.

Use BEFORE triggers to modify data before saving, AFTER triggers for actions after changes.

Keep trigger logic simple and avoid recursion to prevent performance issues.