Triggers in SQL are special types of stored procedures that are automatically executed (or "triggered") when specific events occur in the database, such as INSERT, UPDATE, or DELETE operations. Triggers can be used to enforce business rules, validate input data, maintain audit trails, and synchronize tables.
Syntax
The syntax for creating a trigger varies slightly between different SQL database systems. Here, we'll cover the syntax for creating triggers in SQL Server, MySQL, and PostgreSQL.
Creating Triggers in SQL Server
CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
-- Trigger logic here
END;
Creating Triggers in MySQL
CREATE TRIGGER trigger_name
BEFORE INSERT, UPDATE, DELETE
ON table_name
FOR EACH ROW
BEGIN
-- Trigger logic here
END;
Creating Triggers in PostgreSQL
CREATE TRIGGER trigger_name
AFTER INSERT, UPDATE, DELETE
ON table_name
FOR EACH ROW
EXECUTE FUNCTION function_name();
Example Usage
Consider a table employeeswith the following structure:
employees
emp_id emp_name salary
1 John 50000
2 Jane 75000
3 Jake 45000
4 Jill 90000
We want to create a trigger that logs any changes to this table into an audit table called employees_audit.
employees_audit
audit_id emp_id operation operation_time old_salary new_salary
Trigger in SQL Server
CREATE TRIGGER trg_employees_audit
ON employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
IF EXISTS (SELECT * FROM inserted)
BEGIN
INSERT INTO employees_audit (emp_id, operation, operation_time, old_salary, new_salary)
SELECT emp_id, 'INSERT', GETDATE(), NULL, salary
FROM inserted;
END
IF EXISTS (SELECT * FROM deleted)
BEGIN
INSERT INTO employees_audit (emp_id, operation, operation_time, old_salary, new_salary)
SELECT emp_id, 'DELETE', GETDATE(), salary, NULL
FROM deleted;
END
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
INSERT INTO employees_audit (emp_id, operation, operation_time, old_salary, new_salary)
SELECT d.emp_id, 'UPDATE', GETDATE(), d.salary, i.salary
FROM deleted d
INNER JOIN inserted i ON d.emp_id = i.emp_id;
END
END;
Trigger in MySQL
DELIMITER //
CREATE TRIGGER trg_employees_audit_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO employees_audit (emp_id, operation, operation_time, old_salary, new_salary)
VALUES (NEW.emp_id, 'INSERT', NOW(), NULL, NEW.salary);
END //
CREATE TRIGGER trg_employees_audit_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employees_audit (emp_id, operation, operation_time, old_salary, new_salary)
VALUES (NEW.emp_id, 'UPDATE', NOW(), OLD.salary, NEW.salary);
END //
CREATE TRIGGER trg_employees_audit_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employees_audit (emp_id, operation, operation_time, old_salary, new_salary)
VALUES (OLD.emp_id, 'DELETE', NOW(), OLD.salary, NULL);
END //
DELIMITER ;
Trigger in PostgreSQL
First, you need to create a function that will be executed by the trigger:
CREATE OR REPLACE FUNCTION trg_employees_audit_func()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO employees_audit (emp_id, operation, operation_time, old_salary, new_salary)
VALUES (NEW.emp_id, 'INSERT', NOW(), NULL, NEW.salary);
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO employees_audit (emp_id, operation, operation_time, old_salary, new_salary)
VALUES (NEW.emp_id, 'UPDATE', NOW(), OLD.salary, NEW.salary);
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO employees_audit (emp_id, operation, operation_time, old_salary, new_salary)
VALUES (OLD.emp_id, 'DELETE', NOW(), OLD.salary, NULL);
RETURN OLD;
END IF;
END;
$$ LANGUAGE plpgsql;
Next, create the trigger:
CREATE TRIGGER trg_employees_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION trg_employees_audit_func();
Explanation
- AFTER INSERT, UPDATE, DELETE: Specifies that the trigger should fire after these operations.
- BEFORE INSERT, UPDATE, DELETE: (MySQL) Specifies that the trigger should fire before these operations.
- FOR EACH ROW: Specifies that the trigger should fire once for each row affected by the query.
- IF TG_OP: (PostgreSQL) Checks the operation type (INSERT, UPDATE, DELETE).
- GETDATE(): (SQL Server) and NOW(): (MySQL, PostgreSQL) Functions to get the current date and time.
Using triggers, you can automate auditing, enforce complex business rules, and ensure data integrity across your SQL databases.
