MySQL — Triggers

Basic Trigger

DELIMITER //
CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END //
DELIMITER ;

Trigger Events

Event	Fires When
BEFORE INSERT	Before a row is inserted
AFTER INSERT	After a row is inserted
BEFORE UPDATE	Before a row is updated
AFTER UPDATE	After a row is updated
BEFORE DELETE	Before a row is deleted
AFTER DELETE	After a row is deleted

Audit Log Trigger

CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(20),
    table_name VARCHAR(50),
    record_id INT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE TRIGGER audit_users_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, table_name, record_id)
    VALUES ('UPDATE', 'users', OLD.id);
END //
DELIMITER ;

NEW and OLD Keywords

-- OLD = the existing row value
-- NEW = the new value being set

DELIMITER //
CREATE TRIGGER prevent_negative_salary
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SET NEW.salary = OLD.salary;
    END IF;
END //
DELIMITER ;

Viewing Triggers

SHOW TRIGGERS;
SHOW TRIGGERS LIKE 'users%';

Dropping Triggers

DROP TRIGGER IF EXISTS before_insert_users;

Mini Practice

Write SQL code that:

1. Creates a BEFORE INSERT trigger
2. Creates an AFTER UPDATE trigger for auditing
3. Uses OLD and NEW keywords
4. Views and drops a trigger

Up Next

Continue with Events — MySQL's built-in task scheduler.