One Practical SQL Trigger Example You Can Actually Use

One UPDATE statement. One trigger. One automatic audit record — no extra code required.

Triggers are one of those SQL features that can seem a little mysterious at first, but the basic idea is simple: a trigger lets the database automatically do something when data changes.

In this example, we'll use a trigger to create a basic audit trail.

Whenever a row in an Employees table is updated, the trigger will record the old and new salary values in a separate audit table.

Note: The primary examples use SQL Server / T-SQL syntax. Where behavior or syntax differs meaningfully across SQL Server, MySQL, PostgreSQL, and Oracle, that's called out directly.

What Is a Trigger?

A trigger is a block of SQL that runs automatically in response to an event such as an INSERT, UPDATE, or DELETE.

That means the database can react to changes without relying on someone to remember to run extra code manually.

Step 1: Create the Main Table

-- SQL Server / MySQL / PostgreSQL
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FullName VARCHAR(100),
    Salary DECIMAL(10,2)
);

This syntax works across SQL Server, MySQL, and PostgreSQL. In Oracle, VARCHAR is supported but VARCHAR2 is the preferred type:

-- Oracle
CREATE TABLE Employees (
    EmployeeID NUMBER PRIMARY KEY,
    FullName VARCHAR2(100),
    Salary NUMBER(10,2)
);

Step 2: Insert Some Sample Data

INSERT INTO Employees (EmployeeID, FullName, Salary)
VALUES
(1, 'Alice Johnson', 60000.00),
(2, 'Brian Smith', 72000.00);

Multi-row INSERT with VALUES works across all four databases.

Step 3: Create the Audit Table

-- SQL Server
CREATE TABLE EmployeeSalaryAudit (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeID INT,
    OldSalary DECIMAL(10,2),
    NewSalary DECIMAL(10,2),
    ChangedAt DATETIME DEFAULT GETDATE()
);

This table will store each salary change, including the old value, the new value, and when the change happened.

Auto-incrementing primary keys and current timestamp functions vary across databases:

-- MySQL
CREATE TABLE EmployeeSalaryAudit (
    AuditID INT AUTO_INCREMENT PRIMARY KEY,
    EmployeeID INT,
    OldSalary DECIMAL(10,2),
    NewSalary DECIMAL(10,2),
    ChangedAt DATETIME DEFAULT NOW()
);

-- PostgreSQL
CREATE TABLE EmployeeSalaryAudit (
    AuditID SERIAL PRIMARY KEY,
    EmployeeID INT,
    OldSalary DECIMAL(10,2),
    NewSalary DECIMAL(10,2),
    ChangedAt TIMESTAMP DEFAULT NOW()
);

-- Oracle
CREATE TABLE EmployeeSalaryAudit (
    AuditID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    EmployeeID NUMBER,
    OldSalary NUMBER(10,2),
    NewSalary NUMBER(10,2),
    ChangedAt DATE DEFAULT SYSDATE
);

Key differences:

SQL Server uses IDENTITY(1,1) and GETDATE()
MySQL uses AUTO_INCREMENT and NOW()
PostgreSQL uses SERIAL (or GENERATED ALWAYS AS IDENTITY in newer versions) and NOW()
Oracle uses GENERATED ALWAYS AS IDENTITY and SYSDATE ## Step 4: Create the Trigger

This is where the databases diverge most significantly.

-- SQL Server
CREATE TRIGGER trg_LogSalaryChange
ON Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO EmployeeSalaryAudit (EmployeeID, OldSalary, NewSalary)
    SELECT
        d.EmployeeID,
        d.Salary AS OldSalary,
        i.Salary AS NewSalary
    FROM deleted d
    INNER JOIN inserted i
        ON d.EmployeeID = i.EmployeeID
    WHERE d.Salary <> i.Salary
       OR (d.Salary IS NULL AND i.Salary IS NOT NULL)
       OR (d.Salary IS NOT NULL AND i.Salary IS NULL);
END;

In SQL Server, triggers can access two special logical tables:

deleted contains the old version of updated rows
inserted contains the new version of updated rows The trigger joins those tables on EmployeeID and logs only rows where the salary actually changed.

A note on the WHERE clause: a simple d.Salary <> i.Salary won't catch changes involving NULL values — in SQL, NULL <> anything evaluates to unknown, not true. The additional IS NULL conditions ensure those changes are captured correctly. This applies to all four database versions below.

-- MySQL
CREATE TRIGGER trg_LogSalaryChange
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
    IF OLD.Salary <> NEW.Salary
       OR (OLD.Salary IS NULL AND NEW.Salary IS NOT NULL)
       OR (OLD.Salary IS NOT NULL AND NEW.Salary IS NULL)
    THEN
        INSERT INTO EmployeeSalaryAudit (EmployeeID, OldSalary, NewSalary)
        VALUES (OLD.EmployeeID, OLD.Salary, NEW.Salary);
    END IF;
END;

-- PostgreSQL (requires a trigger function)
-- Note: EXECUTE FUNCTION requires PostgreSQL 11+
-- Use EXECUTE PROCEDURE for older versions
CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.Salary <> NEW.Salary
       OR (OLD.Salary IS NULL AND NEW.Salary IS NOT NULL)
       OR (OLD.Salary IS NOT NULL AND NEW.Salary IS NULL)
    THEN
        INSERT INTO EmployeeSalaryAudit (EmployeeID, OldSalary, NewSalary)
        VALUES (OLD.EmployeeID, OLD.Salary, NEW.Salary);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_LogSalaryChange
AFTER UPDATE ON Employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

-- Oracle
CREATE OR REPLACE TRIGGER trg_LogSalaryChange
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
    IF :OLD.Salary <> :NEW.Salary
       OR (:OLD.Salary IS NULL AND :NEW.Salary IS NOT NULL)
       OR (:OLD.Salary IS NOT NULL AND :NEW.Salary IS NULL)
    THEN
        INSERT INTO EmployeeSalaryAudit (EmployeeID, OldSalary, NewSalary)
        VALUES (:OLD.EmployeeID, :OLD.Salary, :NEW.Salary);
    END IF;
END;

Key differences across databases:

SQL Server uses deleted and inserted virtual tables and handles multiple rows as sets in a single statement-level trigger
MySQL uses OLD and NEW and requires FOR EACH ROW — MySQL only supports row-level triggers
PostgreSQL separates the trigger logic into a reusable trigger function, then attaches it to the table — OLD and NEW are used inside the function. EXECUTE FUNCTION requires PostgreSQL 11 or later; use EXECUTE PROCEDURE for older versions
Oracle uses :OLD and :NEW (note the colon prefix) and requires FOR EACH ROW for row-level triggers ## A Note on Statement-Level vs Row-Level Triggers

This is one of the most important distinctions to understand — and one that can produce unexpected results if you're not aware of it.

A row-level trigger fires once for every row affected by the operation. Update 50 rows and it fires 50 times.

A statement-level trigger fires once per SQL statement, regardless of how many rows were affected.

SQL Server triggers are statement-level by default, which is why the SQL Server example joins deleted and inserted as sets rather than referencing single OLD/NEW values
MySQL only supports row-level triggers
PostgreSQL supports both — FOR EACH ROW for row-level, FOR EACH STATEMENT for statement-level
Oracle supports both — FOR EACH ROW for row-level, statement-level is the default without it This matters in practice: if you run a bulk update affecting 1,000 rows, a row-level trigger fires 1,000 times. For audit logging on large tables, that's worth thinking about before you deploy.

Step 5: Test It

UPDATE Employees
SET Salary = 65000.00
WHERE EmployeeID = 1;

Step 6: Check the Audit Table

SELECT *
FROM EmployeeSalaryAudit;

You should see a row showing that Employee 1's salary changed from 60000.00 to 65000.00.

That happened automatically because of the trigger.

Why This Is Useful

This kind of trigger can be useful when you want the database to help maintain a history of important changes.

Instead of depending on application code to remember to log the update, the database handles it consistently — regardless of which application, tool, or user made the change.

A Quick Caution

Triggers are powerful, but they should be used carefully.

Because they run automatically behind the scenes, they can make database behavior harder to understand if too much hidden logic accumulates. A developer looking at the UPDATE statement alone has no visible indication that something else is happening as a result.

For a simple audit trail like this, though, a trigger can be a practical and readable solution — and audit logging is one of the most widely accepted use cases for triggers across all four major databases.

Final Thought

This is a small example, but it demonstrates the central idea: when data changes, the database can respond automatically.

The syntax looks different depending on which database you're working with — but the concept is the same everywhere. A trigger watches for an event, and when that event happens, it acts.

That's what makes triggers useful — and why they're worth understanding.

SQL Bubble Pop covers the query logic and filtering patterns that form the building blocks of trigger design — through daily challenges and active recall that make these concepts click faster.

Download SQL Bubble Pop free on the App Store. Search "SQL Bubble Pop."

TL;DR

A trigger runs automatically when data changes — no manual intervention needed
SQL Server uses deleted and inserted virtual tables; other databases use OLD and NEW; Oracle adds a colon prefix (:OLD and :NEW)
PostgreSQL requires a separate trigger function before attaching the trigger to a table (EXECUTE FUNCTION requires PostgreSQL 11+)
Row-level triggers fire once per affected row; statement-level triggers fire once per SQL statement — know which you're using
Audit logging is one of the most widely accepted and practical use cases for triggers
Use triggers carefully — hidden logic can make database behavior harder to reason about at scale

推荐订阅源

DEV Community