Examples

Assume you have a table named employees with columns first_name, last_name, department, and salary. You want to create a trigger that sets the salary to a default value of 50000 if it is not provided during an insert.

CREATE TRIGGER set_default_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary IS NULL THEN
        SET NEW.salary = 50000;
    END IF;
END;

Create a table named audit_log to record all insert operations on the employees table.

CREATE TABLE audit_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    action VARCHAR(50),
    employee_id INT,
    action_time DATETIME
);

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, employee_id, action_time)
    VALUES ('INSERT', NEW.id, NOW());
END;

Create a trigger that logs changes to the salary column in the employees table.

CREATE TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO audit_log (action, employee_id, action_time)
        VALUES ('UPDATE SALARY', NEW.id, NOW());
    END IF;
END;

Create a trigger that logs deletions from the employees table.

CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, employee_id, action_time)
    VALUES ('DELETE', OLD.id, NOW());
END;