The Trigger That Silently Dropped a Row

A trigger fires once per statement, not once per row — and code that forgets this passes every test that only inserts one row at a time.

A stored procedure with a parameter

CREATE OR ALTER PROCEDURE GetBooksByAuthor
    @AuthorId INT
AS
BEGIN
    SELECT TOP 5 book_id, CAST(title AS VARCHAR(20)) AS title, price
    FROM books WHERE author_id = @AuthorId ORDER BY book_id;
END
GO
EXEC GetBooksByAuthor @AuthorId = 1;

book_id     title                price
----------- -------------------- --------
          1 Book Title 1            11.99
         51 Book Title 51           21.99
        101 Book Title 101          31.99
        151 Book Title 151          41.99
        201 Book Title 201          11.99

(5 rows affected)

GetBooksByAuthor is a named, reusable, parameterized query — the same benefit a CTE gives within one statement, extended across every statement and application that calls it.

The bug: a trigger that only sees one row

CREATE TABLE price_audit (book_id INT, new_price DECIMAL(6,2));

CREATE OR ALTER TRIGGER trg_book_price_audit
ON books
AFTER INSERT
AS
BEGIN
    DECLARE @id INT, @price DECIMAL(6,2);
    SELECT @id = book_id, @price = price FROM inserted;
    INSERT INTO price_audit (book_id, new_price) VALUES (@id, @price);
END
GO

INSERT INTO books (title, author_id, price, published_year)
VALUES ('New Book A', 1, 15.00, 2026), ('New Book B', 2, 25.00, 2026);

SELECT * FROM price_audit;

(2 rows affected)

book_id     new_price
----------- ---------
       5001     15.00

(1 rows affected)

Two books were inserted (2 rows affected) — only one made it into price_audit. SELECT @id = book_id, @price = price FROM inserted assigns a multi-row result into scalar variables, and SQL Server silently keeps just one row’s values, discarding the rest with no error. New Book B was never audited, and nothing about running this code told you that.

The fix: process inserted as a set

CREATE OR ALTER TRIGGER trg_book_price_audit
ON books
AFTER INSERT
AS
BEGIN
    INSERT INTO price_audit (book_id, new_price)
    SELECT book_id, price FROM inserted;
END
GO

INSERT INTO books (title, author_id, price, published_year)
VALUES ('New Book C', 1, 35.00, 2026), ('New Book D', 2, 45.00, 2026);

SELECT * FROM price_audit;

(2 rows affected)

book_id     new_price
----------- ---------
       5004     45.00
       5003     35.00

(2 rows affected)

Both new books are audited this time. INSERT ... SELECT ... FROM inserted is set-based — it processes every row inserted actually contains, not just one, which is the correct default assumption for any trigger logic: never assume a statement only affects one row.

Practice

CREATE OR ALTER PROCEDURE CountBooksByAuthor
    @AuthorId INT,
    @BookCount INT OUTPUT
AS
BEGIN
    SELECT @BookCount = COUNT(*) FROM books WHERE author_id = @AuthorId;
END
GO

DECLARE @cnt INT;
EXEC CountBooksByAuthor @AuthorId = 1, @BookCount = @cnt OUTPUT;
SELECT @cnt AS book_count;

book_count
-----------
        102

SELECT CAST(name AS VARCHAR(30)) AS trigger_name
FROM sys.triggers WHERE parent_id = OBJECT_ID('books');

trigger_name
------------------------------
trg_book_price_audit

(1 rows affected)

Next: backup and restore — a real database backup, and recovering from a dropped table.