Triggers

Lecture 6 · a trigger is a statement that is executed automatically by the system as a side effect of a modification to the database. Introduced to the SQL standard in SQL:1999, but supported earlier with non-standard syntax by most databases.

Designing a trigger mechanism

a. Specify the CONDITIONS under which the trigger is to be executed

timing (BEFORE/AFTER) + event (INSERT/UPDATE/DELETE) + table

b. Specify the ACTIONS to be taken when the trigger executes

the procedural body between BEGIN … END

Every trigger question reduces to these two halves: when, and what.

Why triggers are used

Enforcing business rules (what a check with a subquery cannot do)
Maintaining audit logs
Validating data — preventing invalid changes
Updating related tables automatically — insertion, update, or deletion in another table
Logging / auditing information
Stopping an operation by raising an error

Two motivating examples

Example 1 — archive an employee who left

When someone leaves the job the row is deleted from employee, and the information of the deleted employee is stored in deleted_employee_log.

Example 1

-- employee(emp_id, name, dept, salary)
-- deleted_employee_log(emp_id, name, dept, salary, deleted_at)

CREATE TRIGGER trg_employee_delete_log
AFTER DELETE ON employee
FOR EACH ROW
BEGIN
    INSERT INTO deleted_employee_log (emp_id, name, dept, salary, deleted_at)
    VALUES (OLD.emp_id, OLD.name, OLD.dept, OLD.salary, NOW());
END;

Example 2 — log a salary change

Example 2

-- employee(emp_id, name, dept, salary)
-- salary_log(emp_id, old_salary, new_salary)

CREATE TRIGGER log_salary_update
AFTER UPDATE ON employee
FOR EACH ROW
BEGIN
    INSERT INTO salary_log(emp_id, old_salary, new_salary)
    VALUES (OLD.emp_id, OLD.salary, NEW.salary);
END;

Syntax in MySQL

MySQL trigger syntax

DELIMITER $$

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- statements;
    -- use NEW.column for INSERT/UPDATE
    -- use OLD.column for DELETE and old values
END$$

DELIMITER ;

OLD / NEW usage in MySQL

INSERT: use NEW.col (no OLD)
DELETE: use OLD.col (no NEW)
UPDATE: use both OLD.col and NEW.col

MySQL rules you must remember

MySQL triggers are row-level only — FOR EACH ROW is required.
Only DML (Data Manipulation Language) triggers are allowed in MySQL.
Allowed timing: BEFORE and AFTER.
Allowed events: INSERT, UPDATE, DELETE → six combinations.
No INSTEAD OF triggers in MySQL (those are common in SQL Server/Oracle, mainly for views).

NOTE!!! Each table can have at most one trigger per timing + event (e.g. only one BEFORE INSERT trigger on employee).

Timing + event	NEW	OLD	What it is for
`BEFORE INSERT`	✔ (writable)	—	Validate / default / normalize the incoming row.
`AFTER INSERT`	✔ (read-only)	—	Audit that a row was created.
`BEFORE UPDATE`	✔ (writable)	✔	Reject an illegal change; recompute derived columns.
`AFTER UPDATE`	✔ (read-only)	✔	Log old → new values.
`BEFORE DELETE`	—	✔	Block the delete by raising an error.
`AFTER DELETE`	—	✔	Archive the deleted row.

Only a BEFORE trigger may assign to NEW.col; in an AFTER trigger the row is already written.

A complete worked example in MySQL

1) Base table and log tables

tables

CREATE TABLE employee (
    emp_id INT PRIMARY KEY,
    name   VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2)
);

CREATE TABLE employee_audit (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    emp_id      INT,
    action      VARCHAR(10),
    note        VARCHAR(200),
    action_time DATETIME
);

CREATE TABLE salary_log (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    emp_id     INT,
    old_salary DECIMAL(10,2),
    new_salary DECIMAL(10,2),
    changed_at DATETIME
);

CREATE TABLE deleted_employee_log (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    emp_id     INT,
    name       VARCHAR(50),
    salary     DECIMAL(10,2),
    deleted_at DATETIME
);

2) BEFORE INSERT — validate salary + set a default if NULL

(1) BEFORE INSERT

CREATE TRIGGER trg_emp_before_insert
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;

    IF NEW.salary IS NULL THEN
        SET NEW.salary = 0;
    END IF;
END$$

3) AFTER INSERT — audit the insert

(2) AFTER INSERT

CREATE TRIGGER trg_emp_after_insert
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit(emp_id, action, note, action_time)
    VALUES (NEW.emp_id, 'INSERT', 'New employee added', NOW());
END$$

4) BEFORE UPDATE — block a salary decrease

(3) BEFORE UPDATE

CREATE TRIGGER trg_emp_before_update
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
    IF NEW.salary < OLD.salary THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary decrease is not allowed';
    END IF;

    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
END$$

5) AFTER UPDATE — log the salary change

(4) AFTER UPDATE

CREATE TRIGGER trg_emp_after_update
AFTER UPDATE ON employee
FOR EACH ROW
BEGIN
    IF NEW.salary <> OLD.salary THEN
        INSERT INTO salary_log(emp_id, old_salary, new_salary, changed_at)
        VALUES (NEW.emp_id, OLD.salary, NEW.salary, NOW());
    END IF;

    INSERT INTO employee_audit(emp_id, action, note, action_time)
    VALUES (NEW.emp_id, 'UPDATE', 'Employee record updated', NOW());
END$$

6) BEFORE DELETE — prevent deleting high-salary employees

(5) BEFORE DELETE

CREATE TRIGGER trg_emp_before_delete
BEFORE DELETE ON employee
FOR EACH ROW
BEGIN
    IF OLD.salary > 100000 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Cannot delete employee with salary > 100000';
    END IF;
END$$

7) AFTER DELETE — store the deleted row

(6) AFTER DELETE

CREATE TRIGGER trg_emp_after_delete
AFTER DELETE ON employee
FOR EACH ROW
BEGIN
    INSERT INTO deleted_employee_log(emp_id, name, salary, deleted_at)
    VALUES (OLD.emp_id, OLD.name, OLD.salary, NOW());

    INSERT INTO employee_audit(emp_id, action, note, action_time)
    VALUES (OLD.emp_id, 'DELETE', 'Employee deleted', NOW());
END$$

Raising an error is how a trigger refuses an operation:SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '…'. SQLSTATE 45000 means “unhandled user-defined exception”. The statement is aborted and the transaction rolled back.

Why DELIMITER $$? The trigger body contains semicolons. Without changing the client delimiter, MySQL would end the CREATE TRIGGER statement at the first internal ; and throw a syntax error. Always restore it with DELIMITER ; afterwards.

Applied in the project: Task 2 — three HR triggers → · Next: SQL Functions →