MySQL - Triggers in MySQL

1. What is a Trigger?

A trigger in MySQL is a set of SQL statements that automatically execute when a certain event happens on a table (like INSERT, UPDATE, or DELETE).

They are useful for enforcing business rules, logging, auditing, or automatic updates.

2. Trigger Events in MySQL

A trigger can be defined for:

BEFORE INSERT → Executes before a new row is inserted.
AFTER INSERT → Executes after a new row is inserted.
BEFORE UPDATE → Executes before a row is updated.
AFTER UPDATE → Executes after a row is updated.
BEFORE DELETE → Executes before a row is deleted.
AFTER DELETE → Executes after a row is deleted.

3. Syntax

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- SQL statements here
END;

4. Examples of Triggers

Example 1: Log inserts into a table

Suppose you have an employees table and a log table.
You want to record every new employee automatically.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50)
);

CREATE TABLE employees_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100),
    action VARCHAR(50),
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employees_log(emp_name, action)
    VALUES (NEW.name, 'INSERT');
END;
//
DELIMITER ;

Whenever a new employee is added, an entry is automatically written to employees_log.

Example 2: Prevent negative salary updates

DELIMITER //
CREATE TRIGGER before_salary_update
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;
//
DELIMITER ;

This trigger stops updates if someone tries to set salary to a negative number.

Example 3: Maintain audit trail on delete

DELIMITER //
CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employees_log(emp_name, action)
    VALUES (OLD.name, 'DELETE');
END;
//
DELIMITER ;

When a record is deleted, the old data (OLD) is logged.

5. Benefits

Enforce rules automatically.
Keep audit/history logs without writing extra code in the app.
Maintain data integrity.

6. Limitations

Triggers apply only to one table.
They can make debugging harder (hidden logic).
Too many triggers can slow performance.

Real-world use cases:

Logging all changes in a banking system.
Automatically updating stock when a new order is inserted.
Enforcing data validation rules (e.g., no negative amounts).