Oracle PL/SQL DML Trigger T-SQL · Triggers · DML Trigger

T-SQL DML Triggers

Master T-SQL DML triggers — AFTER and INSTEAD OF triggers, INSERTED/DELETED pseudo-tables, distinguishing INSERT/UPDATE/DELETE in multi-event triggers, capturing old vs new values via JOIN, the UPDATE() function for column-level checks, INSTEAD OF triggers on views, and set-based trigger patterns.

What is a DML Trigger?

A DML trigger is a piece of T-SQL that runs automatically in response to INSERT, UPDATE, or DELETE on a particular table or view. The two main flavors are AFTER (run after the statement, before commit) and INSTEAD OF (replace the statement entirely).

DML triggers are most often used for auditing, validating multi-table rules, maintaining derived data, and making complex views updatable.

The inserted and deleted Pseudo-Tables

Every DML trigger has access to two special tables that contain the affected rows. They have the same columns as the target table:

DML statement	`inserted`	`deleted`
`INSERT`	The new rows	(empty)
`DELETE`	(empty)	The removed rows
`UPDATE`	The post-update rows	The pre-update rows

The pseudo-tables exist only inside the trigger and only for the duration of one trigger fire.

⚠️ Triggers fire ONCE PER STATEMENT, not once per row. One UPDATE that touches 1,000 rows fires the trigger once, with 1,000 rows in inserted and deleted. Code like SELECT @id = id FROM inserted silently grabs an arbitrary row — it works in the demo and breaks in production. Always write set-based trigger logic.

Syntax

CREATE [OR ALTER] TRIGGER trigger_name ON table_or_view { AFTER | INSTEAD OF } { INSERT, UPDATE, DELETE } AS BEGIN -- can use 'inserted' and 'deleted' pseudo-tables END;

Notes:

FOR is a synonym for AFTER — the two are interchangeable in DML triggers. Most modern code uses AFTER.
You can list one, two, or all three events: AFTER INSERT, UPDATE, DELETE creates a single trigger that handles them all.
INSTEAD OF triggers can sit on tables and views; AFTER triggers can only sit on tables.

AFTER Triggers

An AFTER trigger runs after the DML statement has been processed but before the surrounding transaction commits. The data has already been changed in the table, and the pseudo-tables are populated. If the trigger calls ROLLBACK, the original DML and any other work in the transaction are undone.

AFTER Trigger Examples

Example 1 — AFTER INSERT Audit Log

SQL — Setup

CREATE TABLE Employees (
    EmployeeID  INT PRIMARY KEY,
    FirstName   NVARCHAR(50),
    LastName    NVARCHAR(50),
    DateOfBirth DATE,
    HireDate    DATE,
    Salary      DECIMAL(10, 2)
);

INSERT INTO Employees VALUES
    (1, 'John',    'Doe',      '1990-01-15', '2015-05-10', 50000.00),
    (2, 'Jane',    'Smith',    '1988-07-20', '2016-02-28', 60000.00),
    (3, 'Alice',   'Johnson',  '1995-03-12', '2017-11-15', 55000.00),
    (4, 'Bob',     'Williams', '1992-09-30', '2018-08-20', 52000.00),
    (5, 'Eve',     'Brown',    '1987-04-25', '2019-04-05', 70000.00),
    (6, 'Charlie', 'Jones',    '1993-11-18', '2020-01-10', 48000.00),
    (7, 'Grace',   'Anderson', '1996-06-05', '2021-03-15', 58000.00);

CREATE TABLE AuditLog (
    LogID       INT IDENTITY(1,1) PRIMARY KEY,
    EventType   NVARCHAR(50),
    EventDate   DATETIME2 DEFAULT SYSDATETIME(),
    Description NVARCHAR(255)
);

T-SQL — Set-based AFTER INSERT trigger

CREATE OR ALTER TRIGGER trgEmployeeInserted
ON Employees
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- One log row per inserted employee — handles single OR multi-row inserts
    INSERT INTO AuditLog (EventType, Description)
    SELECT 'Employee Inserted',
           'Inserted ID=' + CAST(EmployeeID AS VARCHAR(10)) +
           ', Name=' + FirstName + ' ' + LastName
    FROM   inserted;
END;
GO

T-SQL — Test it

INSERT INTO Employees (EmployeeID, FirstName, LastName, DateOfBirth, HireDate, Salary)
VALUES (8, 'Dhileep', 'Kumar', '2002-06-22', '2021-05-26', 60000.00);

SELECT * FROM AuditLog;

OUTPUT

Results

(1 row affected)

LogID EventType EventDate Description

───── ───────────────── ─────────────────────── ────────────────────────────────────

1 Employee Inserted 2026-05-03 14:23:05.123 Inserted ID=8, Name=Dhileep Kumar

💡 Notice the set-based pattern. The INSERT in the trigger uses INSERT INTO ... SELECT ... FROM inserted — one row in, one log row out; one hundred rows in, one hundred log rows out. Never read variables from the pseudo-table unless you explicitly handle the multi-row case.

Example 2 — Single Trigger for INSERT, UPDATE, DELETE

One trigger can handle all three events. Inside the body, distinguish the operation by checking which pseudo-tables are populated:

Operation	`inserted`	`deleted`
INSERT	has rows	empty
UPDATE	has rows	has rows
DELETE	empty	has rows

SQL — Operation log table

CREATE TABLE OperationLog (
    LogID         INT IDENTITY(1,1) PRIMARY KEY,
    TableName     NVARCHAR(255),
    OperationType NVARCHAR(50),
    OperationDate DATETIME2 DEFAULT SYSDATETIME(),
    AffectedRows  INT
);

T-SQL — Multi-event trigger

CREATE OR ALTER TRIGGER trgEmployeeChanges
ON Employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @op       NVARCHAR(50);
    DECLARE @rowCount INT;

    -- Pseudo-tables drive the operation type
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        SET @op       = 'UPDATE';
        SELECT @rowCount = COUNT(*) FROM inserted;
    END
    ELSE IF EXISTS (SELECT 1 FROM inserted)
    BEGIN
        SET @op       = 'INSERT';
        SELECT @rowCount = COUNT(*) FROM inserted;
    END
    ELSE IF EXISTS (SELECT 1 FROM deleted)
    BEGIN
        SET @op       = 'DELETE';
        SELECT @rowCount = COUNT(*) FROM deleted;
    END
    ELSE
        RETURN;       -- 0-row UPDATE — nothing to log

    INSERT INTO OperationLog (TableName, OperationType, AffectedRows)
    VALUES ('Employees', @op, @rowCount);
END;
GO

📌 Why the early RETURN? An UPDATE with a WHERE that matches no rows still fires the trigger — but both pseudo-tables are empty. The early-exit pattern avoids logging "phantom" operations.

T-SQL — Test all three operations

INSERT INTO Employees (EmployeeID, FirstName, LastName, DateOfBirth, HireDate, Salary)
VALUES (9, 'Test', 'User', '2000-01-01', '2024-01-01', 60000.00);

UPDATE Employees SET Salary = 65000.00 WHERE EmployeeID = 9;

DELETE FROM Employees WHERE EmployeeID = 9;

SELECT * FROM OperationLog ORDER BY LogID;

OUTPUT

Results

(3 rows affected)

LogID TableName OperationType OperationDate AffectedRows

───── ────────── ───────────── ─────────────────────── ────────────

1 Employees INSERT 2026-05-03 14:23:05.123 1

2 Employees UPDATE 2026-05-03 14:23:05.456 1

3 Employees DELETE 2026-05-03 14:23:05.789 1

Example 3 — Capturing Old vs New Values on UPDATE

The full power of inserted and deleted on UPDATE — join them by primary key to see what each row was before and after:

SQL — Salary history table

CREATE TABLE SalaryHistory (
    HistID       INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeID   INT,
    OldSalary    DECIMAL(10, 2),
    NewSalary    DECIMAL(10, 2),
    ChangedAt    DATETIME2 DEFAULT SYSDATETIME()
);

T-SQL — Salary-change trigger

CREATE OR ALTER TRIGGER trgSalaryAudit
ON Employees
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Only log when Salary actually changed
    IF UPDATE(Salary)
    BEGIN
        INSERT INTO SalaryHistory (EmployeeID, OldSalary, NewSalary)
        SELECT i.EmployeeID, d.Salary, i.Salary
        FROM   inserted i
               JOIN deleted d ON d.EmployeeID = i.EmployeeID
        WHERE  d.Salary <> i.Salary;     -- skip rows where Salary was unchanged
    END
END;
GO

-- Trigger an update
UPDATE Employees SET Salary = 75000 WHERE EmployeeID = 1;

SELECT * FROM SalaryHistory;

OUTPUT

Results

(1 row affected)

HistID EmployeeID OldSalary NewSalary ChangedAt

────── ────────── ───────── ───────── ───────────────────────

1 1 50000.00 75000.00 2026-05-03 14:23:05.123

💡 UPDATE(column_name) returns TRUE if the column appeared in the SET clause of the triggering UPDATE — useful for skipping audit work when the watched column wasn't touched. There's also COLUMNS_UPDATED() for checking multiple columns via a bitmask.

INSTEAD OF Triggers

An INSTEAD OF trigger replaces the DML statement entirely. The original INSERT/UPDATE/DELETE never runs against the underlying table — the trigger is the operation. Most often used to make complex views updatable, or to redirect a write to a different table.

Aspect	AFTER trigger	INSTEAD OF trigger
When it runs	After the DML statement	Replaces the DML statement
Default DML executes?	Yes	No (you re-issue it manually if you want)
Can sit on views?	No	Yes
Pseudo-tables populated?	Yes (rows already in table)	Yes (rows you'd like to apply)

Example 4 — INSTEAD OF INSERT on a View

A view that joins two tables can't be inserted into directly — but an INSTEAD OF trigger lets you support inserts by routing them to the underlying tables:

SQL — Tables and joinable view

CREATE TABLE Departments (
    DeptID    INT PRIMARY KEY,
    DeptName  VARCHAR(50)
);

CREATE TABLE Staff (
    EmpID     INT PRIMARY KEY,
    EmpName   VARCHAR(50),
    DeptID    INT
);

INSERT INTO Departments VALUES (1, 'IT'), (2, 'HR');
INSERT INTO Staff       VALUES (1, 'John Doe', 1), (2, 'Jane Smith', 2);

GO

CREATE VIEW StaffView AS
SELECT s.EmpID, s.EmpName, d.DeptName
FROM   Staff s
       JOIN Departments d ON d.DeptID = s.DeptID;
GO

T-SQL — Make the view insertable

CREATE OR ALTER TRIGGER InsertStaffView
ON StaffView
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Staff (EmpID, EmpName, DeptID)
    SELECT i.EmpID, i.EmpName, d.DeptID
    FROM   inserted i
           JOIN Departments d ON d.DeptName = i.DeptName;
END;
GO

INSERT INTO StaffView (EmpID, EmpName, DeptName)
VALUES (3, 'Alice', 'IT');

SELECT * FROM Staff;

OUTPUT

Results

(3 rows affected)

EmpID EmpName DeptID

───── ────────── ──────

1 John Doe 1

2 Jane Smith 2

3 Alice 1

Best Practices

Always write set-based logic. Never assume inserted or deleted has exactly one row.
Add SET NOCOUNT ON; at the top — avoids spurious "N rows affected" messages confusing client code.
Keep triggers short. They run inside someone else's transaction and hold its locks. No external calls, no complex multi-table joins.
Use UPDATE(col) or COLUMNS_UPDATED() to skip work when the watched column wasn't actually changed.
Early-exit on zero-row events: IF NOT EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted) RETURN;
Don't put business logic that belongs in the application layer in triggers. Triggers are good for invariants and audit trails — bad for orchestration.
Document trigger existence loudly. Most ops issues with triggers come from people not realizing they exist.

Conclusion

DML triggers fire on INSERT, UPDATE, DELETE; can be AFTER (most common) or INSTEAD OF (replaces the DML, works on views).
FOR is just a synonym for AFTER — the two are interchangeable.
Inside the trigger, inserted and deleted hold the affected rows — empty for irrelevant operations, both populated for UPDATE.
Triggers fire once per statement; pseudo-tables can have many rows. Always think set-based.
Distinguish operations by checking which pseudo-tables have rows; use UPDATE(col) for column-level work.
One trigger can handle multiple events — AFTER INSERT, UPDATE, DELETE.
Keep trigger code small, fast, and well-documented; expensive triggers slow down every DML they fire on.