How does an SQL trigger work?

What is an SQL trigger

An SQL trigger is a database object representing a special type of stored procedure that automatically executes when a specific event occurs in the database. These events may include INSERT, UPDATE, or DELETE operations on tables or views.

How an SQL trigger works

A trigger is automatically activated in response to a specified event (e.g., adding a new record to a table).

Types of triggers

• BEFORE: Works before the operation is executed.
  Used to validate or modify data before it is saved.
• AFTER: Works after the operation is executed.
  Often used for logging or updating related tables.
• INSTEAD OF: Replaces the default behavior of the operation.
  Used when you need to override the default action, such as when working with views.

Within a trigger, you can access old and new data values through special pseudo-tables: OLD and NEW.

Example of using a trigger

Suppose we have an Employees table, and we want to automatically save a history of salary changes in the SalaryHistory table whenever employee data is updated.

Creating tables:

MySQL 8.1
-- Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Salary DECIMAL(10, 2)
);

-- Salary history table
CREATE TABLE SalaryHistory (
    EmployeeID INT,
    OldSalary DECIMAL(10, 2),
    NewSalary DECIMAL(10, 2),
    ChangeDate DATETIME DEFAULT CURRENT_TIMESTAMP
);

Creating a trigger::

MySQL 8.1
DELIMITER $$

CREATE TRIGGER trg_AfterSalaryUpdate
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
    IF OLD.Salary <> NEW.Salary THEN
        INSERT INTO SalaryHistory (EmployeeID, OldSalary, NewSalary)
        VALUES (NEW.EmployeeID, OLD.Salary, NEW.Salary);
    END IF;
END$$

DELIMITER ;

When to use triggers

• Logging and auditing:
  Automatically logging data changes to track user actions.
• Maintaining data integrity:
  Enforcing complex business rules that cannot be achieved through constraints.
• Data synchronization:
  Automatically updating or modifying related tables when data changes.
• Calculating values:
  Automatically calculating and updating aggregated or derived data.