Triggers
A trigger is code that SQL Server runs automatically when rows are inserted, updated or deleted. This lesson builds an audit trigger that works for many rows at once, shows INSTEAD OF triggers, and explains what triggers cost on large data.
- StartThe app changes dataUPDATE Products SET Price = Price * 1.1 WHERE CategoryId = 3
- Step 2SQL Server changes the rowsThe rows are updated inside a transaction
- Step 3inserted and deleted are filleddeleted holds the old rows, inserted the new rows, for every changed row
- Step 4The AFTER trigger runs onceOne run per statement, whether 1 row or 1 million rows changed
- Step 5The trigger writes audit rowsINSERT INTO PriceAudit SELECT ... FROM inserted JOIN deleted
- EndCommit or roll back togetherIf the trigger fails, the original UPDATE is undone as well
The idea in plain words
A DML trigger is attached to a table and fires after (or instead of) an INSERT, UPDATE or DELETE. Inside it, two special tables are available: inserted holds the new version of the changed rows and deleted holds the old version. An update has both.
A trigger runs inside the same transaction as the statement that fired it. If the trigger fails or calls ROLLBACK, the original change is undone too.
Example: an audit trail for price changes
The trigger fires once per statement, not once per row, so it must handle any number of rows. Joining inserted to deleted does exactly that.
CREATE TABLE dbo.PriceAudit (
AuditId bigint IDENTITY PRIMARY KEY,
ProductId int NOT NULL,
OldPrice decimal(10,2) NOT NULL,
NewPrice decimal(10,2) NOT NULL,
ChangedAt datetime2 NOT NULL DEFAULT SYSUTCDATETIME(),
ChangedBy sysname NOT NULL DEFAULT SUSER_SNAME()
);
GO
CREATE OR ALTER TRIGGER dbo.trg_Products_PriceAudit
ON dbo.Products
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF NOT UPDATE(Price) RETURN;
INSERT INTO dbo.PriceAudit (ProductId, OldPrice, NewPrice)
SELECT d.ProductId, d.Price, i.Price
FROM inserted i
JOIN deleted d ON d.ProductId = i.ProductId
WHERE i.Price <> d.Price;
END;INSTEAD OF triggers
An INSTEAD OF trigger replaces the statement. A common use is a soft delete: the app runs a normal DELETE, and the trigger marks the rows as deleted instead of removing them.
CREATE OR ALTER TRIGGER dbo.trg_Customers_SoftDelete
ON dbo.Customers
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
UPDATE c SET IsDeleted = 1, DeletedAt = SYSUTCDATETIME()
FROM dbo.Customers c
JOIN deleted d ON d.CustomerId = c.CustomerId;
END;Triggers and huge data
Every trigger adds work to every write, so it matters most on busy or very large tables.
- An UPDATE of 1 million rows fires the trigger once, with 1 million rows in inserted and deleted. Set-based code handles that; a cursor or loop inside a trigger does not.
- The trigger holds the statement's locks until it finishes, so a slow trigger blocks other users. Keep triggers short and avoid calling other systems from them.
- BULK INSERT and SqlBulkCopy skip triggers unless you ask for them (FIRE_TRIGGERS or SqlBulkCopyOptions.FireTriggers).
- For history or change feeds on big tables, temporal tables or change data capture are often better than hand-written triggers.
Common mistakes
- Assuming one row: SELECT @id = ProductId FROM inserted silently ignores every row but one.
- Hiding important business rules in triggers, where developers reading the app code cannot see them.
- Triggers that update their own table and fire again (recursive or nested triggers).
- Forgetting SET NOCOUNT ON, which sends extra row-count messages that can confuse some clients.
Interview question
What are the inserted and deleted tables in a SQL Server trigger?
They are temporary, read-only tables available only inside a DML trigger. For an INSERT, inserted holds the new rows and deleted is empty. For a DELETE, deleted holds the removed rows. For an UPDATE, deleted holds the rows before the change and inserted holds them after, so joining the two on the key shows old and new values. Because a trigger fires once per statement, these tables can contain many rows.
Want the full details? Read the official Microsoft documentation for this topic.