MySQL Triggers

A trigger in MySQL is a set of SQL statements that automatically execute in response to a specific event on a table. The event can be an INSERT, UPDATE, or DELETE operation. Triggers run automatically — no manual call is needed.

Why Use Triggers?

• Automatic auditing — Log every change made to a table without application-level code.
• Data validation — Enforce business rules at the database level.
• Cascading actions — Automatically update related tables when data changes.
• Preventing bad data — Block or correct invalid values before they are saved.

Trigger Timing

Trigger Events

Triggers respond to one of three events: INSERT, UPDATE, or DELETE. This gives six combinations: BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, AFTER DELETE.

NEW and OLD Keywords

• NEW — Refers to the new row value (available in INSERT and UPDATE triggers)
• OLD — Refers to the old row value (available in UPDATE and DELETE triggers)

Syntax

DELIMITER //

CREATE TRIGGER trigger_name
BEFORE | AFTER INSERT | UPDATE | DELETE
ON table_name
FOR EACH ROW
BEGIN
    -- SQL statements
END //

DELIMITER ;

Example: AFTER INSERT — Log New Employee

CREATE TABLE employee_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_id INT,
    action VARCHAR(50),
    log_time DATETIME
);

DELIMITER //

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_log (emp_id, action, log_time)
    VALUES (NEW.emp_id, 'INSERT', NOW());
END //

DELIMITER ;

Every time a new employee is added, a log entry is created automatically.

Example: BEFORE UPDATE — Prevent Salary Reduction

DELIMITER //

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

DELIMITER ;

If an attempt is made to reduce a salary, the trigger raises an error and blocks the update.

Example: AFTER DELETE — Archive Deleted Record

CREATE TABLE deleted_employees LIKE employees;

DELIMITER //

CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO deleted_employees
    VALUES (OLD.emp_id, OLD.emp_name, OLD.department, OLD.salary, OLD.city);
END //

DELIMITER ;

Viewing Triggers

SHOW TRIGGERS FROM your_database_name;

Dropping a Trigger

DROP TRIGGER IF EXISTS after_employee_insert;

Key Points

• Triggers execute automatically when INSERT, UPDATE, or DELETE occurs on a table.
• BEFORE triggers run before the data change; AFTER triggers run after.
• NEW refers to the incoming data; OLD refers to the existing data.
• Use SIGNAL SQLSTATE inside a BEFORE trigger to reject invalid changes.
• Each table can have up to six triggers — one per event and timing combination.