Database Triggers & Audit Logs

📄 MySQLLesson 13Advanced

Database triggers execute automatically when insert, update, or delete commands are run on tables.

1 Audit Log Trigger Example

SQL — Trigger Syntax

CREATE TABLE user_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(50),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, action)
    VALUES (NEW.id, 'CREATED');
END //
DELIMITER ;

2 Code Challenge

Challenge: Write a trigger tracking product price updates, saving previous price, new price, and change timestamps in an audit log table.