Why triggers automate responses in MySQL

Introduction

Triggers automatically run a set of actions when something changes in the database. This helps keep data correct and saves time by doing tasks without needing to ask.

When you want to log changes to important data automatically.

When you need to check or change data right after it is added or updated.

When you want to keep related tables in sync without manual work.

When you want to enforce rules like preventing bad data from being saved.

When you want to send alerts or notifications after certain changes happen.

Syntax

MySQL

CREATE TRIGGER trigger_name
  BEFORE|AFTER INSERT|UPDATE|DELETE
  ON table_name
  FOR EACH ROW
  BEGIN
    -- SQL statements to run automatically
  END;

Triggers run automatically before or after data changes like insert, update, or delete.

You write the SQL code inside the BEGIN and END block to define what happens.

Examples

This trigger adds a record to an audit log every time a new employee is added.

MySQL

CREATE TRIGGER log_insert
  AFTER INSERT ON employees
  FOR EACH ROW
  BEGIN
    INSERT INTO audit_log(action, emp_id) VALUES('insert', NEW.id);
  END;

This trigger stops any update that tries to set a negative salary.

MySQL

CREATE TRIGGER check_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 two tables: one for employees and one for logging actions. The trigger adds a log entry every time a new employee is added. Then it inserts one employee and shows the log.

MySQL

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

CREATE TABLE audit_log (
  id INT PRIMARY KEY AUTO_INCREMENT,
  action VARCHAR(20),
  emp_id INT,
  action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER after_employee_insert
  AFTER INSERT ON employees
  FOR EACH ROW
  BEGIN
    INSERT INTO audit_log(action, emp_id) VALUES('insert', NEW.id);
  END$$
DELIMITER ;

INSERT INTO employees(name, salary) VALUES('Alice', 50000);
SELECT * FROM audit_log;

Important Notes

Triggers can slow down data changes if they do a lot of work, so keep them simple.

Use triggers carefully to avoid unexpected side effects or loops.

Summary

Triggers run automatically when data changes happen.

They help keep data correct and automate tasks.

You write triggers using CREATE TRIGGER with BEFORE or AFTER events.