SQL · Pattern 13 — Database Objects
Triggers
A trigger is a special type of stored program that automatically executes when INSERT, UPDATE, or DELETE operations occur.
Source data — Pattern 13: Database Objects
All queries run against the shared employees table (see schema.sql). This pattern introduces commonly used database objects including views, stored procedures, user-defined functions, and triggers. All examples are built using the existing employees table.
What it does
A trigger is a special type of stored program that automatically executes when INSERT, UPDATE, or DELETE operations occur.
Problem
Log salary updates automatically.
CREATE TRIGGER trg_salary_update
ON employees
AFTER UPDATE
AS
BEGIN
INSERT INTO salary_log(employee_id,old_salary,new_salary)
SELECT d.employee_id,d.salary,i.salary
FROM deleted d
JOIN inserted i
ON d.employee_id=i.employee_id;
END;
Result
