SQL for .NET developers Advanced

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.

What happens when a trigger fires
  1. StartThe app changes dataUPDATE Products SET Price = Price * 1.1 WHERE CategoryId = 3
  2. Step 2SQL Server changes the rowsThe rows are updated inside a transaction
  3. Step 3inserted and deleted are filleddeleted holds the old rows, inserted the new rows, for every changed row
  4. Step 4The AFTER trigger runs onceOne run per statement, whether 1 row or 1 million rows changed
  5. Step 5The trigger writes audit rowsINSERT INTO PriceAudit SELECT ... FROM inserted JOIN deleted
  6. 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.

SQL
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.

SQL
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.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.