Logical Tables in SQL Server: Inserted and Deleted Logical Table

Understand the temporary tables SQL Server creates automatically inside triggers.

Inside a SQL Server trigger, two special logical tables — Inserted and Deleted — give you access to the data affected by the triggering statement. Understanding them is essential to writing correct triggers.

What Is the Inserted Table?

Holds the new version of rows affected by an INSERT or UPDATE statement.

CREATE TRIGGER trg_AfterInsert
ON employees
AFTER INSERT
AS
BEGIN
  SELECT * FROM Inserted;
END;

What Is the Deleted Table?

Holds the old version of rows affected by a DELETE or UPDATE statement.

CREATE TRIGGER trg_AfterDelete
ON employees
AFTER DELETE
AS
BEGIN
  SELECT * FROM Deleted;
END;

How UPDATE Uses Both Tables

An UPDATE is logically treated as a delete followed by an insert — so the old row values appear in Deleted, and the new values appear in Inserted, letting you compare before-and-after states.

SELECT i.employee_id, d.salary AS old_salary, i.salary AS new_salary
FROM Inserted i
JOIN Deleted d ON i.employee_id = d.employee_id;

Common Use Cases

• Building audit logs that record who changed what, and when
• Validating that a column's new value falls within an allowed range
• Preventing specific updates by checking old vs new values before allowing a change