INSTEAD OF Triggers — Intercepting INSERT, UPDATE and DELETE

An INSTEAD OF trigger replaces the DML operation that fired it — when an INSERT, UPDATE, or DELETE is attempted, the trigger runs instead. This enables writing to non-updatable views (routing the write to correct underlying tables), implementing soft deletes at the database level, and intercepting writes to enforce complex business rules without changing application code. The inserted and deleted virtual tables inside the trigger contain the rows that would have been affected.

INSTEAD OF Triggers #

-- ── INSTEAD OF INSERT on a view — route writes to correct tables ──────────
-- The view vw_PublishedPostSummary is normally read-only.
-- This trigger handles INSERT attempts against it.

CREATE OR ALTER TRIGGER trg_vw_PublishedPost_Insert
ON dbo.vw_PublishedPostSummary
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- The inserted virtual table contains the rows the caller tried to insert
    INSERT INTO dbo.Posts (AuthorId, Title, Slug, Body, Status, IsPublished, PublishedAt)
    SELECT
        i.AuthorId,    -- caller must supply these
        i.Title,
        i.Slug,
        i.Body,
        'published',   -- enforce status
        1,
        SYSUTCDATETIME()
    FROM inserted i;   -- 'inserted' = virtual table of rows being inserted
END;
GO

-- ── INSTEAD OF DELETE — soft delete instead of physical removal ────────────
-- When a Post is deleted, mark it as archived rather than removing the row

CREATE OR ALTER TRIGGER trg_Posts_SoftDelete
ON dbo.Posts
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.Posts
    SET    Status    = 'archived',
           IsPublished = 0,
           UpdatedAt = SYSUTCDATETIME()
    FROM   dbo.Posts p
    JOIN   deleted   d ON d.Id = p.Id;  -- 'deleted' = rows the caller tried to delete

    -- Also hide associated comments
    UPDATE dbo.Comments
    SET    IsApproved = 0
    WHERE  PostId IN (SELECT Id FROM deleted);
END;
GO

-- ── Verify the trigger fires on DELETE ───────────────────────────────────
DELETE FROM dbo.Posts WHERE Id = 42;
-- Row is NOT deleted — it is archived (Status = 'archived', IsPublished = 0)
SELECT Id, Title, Status, IsPublished FROM dbo.Posts WHERE Id = 42;

-- ── Trigger metadata ──────────────────────────────────────────────────────
SELECT t.name, t.type_desc, o.name AS TableOrView
FROM   sys.triggers t
JOIN   sys.objects  o ON o.object_id = t.parent_id
WHERE  o.name IN ('Posts', 'vw_PublishedPostSummary');

-- ── Disable and enable triggers ───────────────────────────────────────────
DISABLE TRIGGER trg_Posts_SoftDelete ON dbo.Posts;  -- e.g. during bulk import
-- ... bulk operations ...
ENABLE  TRIGGER trg_Posts_SoftDelete ON dbo.Posts;

Common Mistakes #

Mistake 1 — Trigger assumes single-row operation (breaks on multi-row DML) #

❌ Wrong — DECLARE @Id INT = (SELECT Id FROM deleted); deleting 3 rows causes subquery to return multiple values; error.

✅ Correct — always JOIN against the inserted/deleted virtual tables as a set: JOIN deleted d ON d.Id = p.Id.

Mistake 2 — Trigger on high-volume table without measuring overhead #

❌ Wrong — audit trigger on a PostViewLog table receiving 10,000 inserts/minute; trigger overhead causes blocking.

✅ Correct — benchmark trigger overhead; for high-volume tables, use application-level audit or a separate async audit queue.