SQL Server triggers, tried in open court. The prosecution says they’re slow, hidden and wrong. The defence says they’re the only witness in the room. Four exhibits were measured on SQL Server 2025, and the judge rules at the end.
The clerk reads the charge. The defendant is an AFTER INSERT trigger on an inventory table. It stands accused of three counts: slowing every insert, auditing the wrong number of rows, and acting without anyone’s knowledge.
The defendant pleads not guilty to all three and asks for a fourth charge to be added in its favour.
Exhibit A: the cost of being there
The prosecution: “One hundred batches of one thousand rows each. The same insert, three ways. Let the record show the numbers.”
No audit at all 100,000 rows 473 ms 2,262 KB of log AFTER INSERT trigger 100,000 rows 998 ms 4,170 KB of log OUTPUT clause into audit 100,000 rows 837 ms 4,144 KB of log
“The trigger run took 998 milliseconds. Without any audit, 473. It nearly doubled the log.”
The defence: “Objection to the word doubled. The log grew because a second table was written, and it grew by the same amount either way. The OUTPUT clause wrote 4,144 KB. My client wrote 4,170. That’s the price of an audit, not the price of a trigger.”
The court: Noted. Over 100,000 rows the trigger run cost 161 milliseconds more than the OUTPUT run. The log figures are bytes written to the file during each run, and the audit accounts for the difference.
Exhibit B: the one-row assumption
The prosecution: “I call a second trigger, written the way most of them are written.”
CREATE TRIGGER dbo.tr_OneRow ON dbo.Inv_OneRow AFTER INSERT AS
BEGIN
DECLARE @id INT, @sku INT;
SELECT @id = id, @sku = sku FROM inserted;
INSERT #audit VALUES (@id, @sku);
END“I inserted one thousand rows in one statement. How many did the audit record?”
The witness: “One.”
The prosecution: “One. Out of a thousand. No error, no warning. Nine hundred and ninety-nine rows walked in and were never seen.”
The defence: “The trigger fires once per statement, and the inserted table holds every row. My client’s author assigned a thousand rows to a scalar variable and kept whichever one came last. That’s not a trigger defect. That’s a developer writing a trigger the way they’d write a loop.”
The court: The distinction is real and the outcome is the same. A trigger that assumes one row fails silently on the first multi-row insert. Every trigger must be written as a set.
Exhibit C: acting without anyone’s knowledge
The prosecution: “The insert statement in the application reads INSERT dbo.Inv_Trig (sku, qty). Nothing in it mentions an audit table. A developer reading the code has no idea a second write happens.”
The defence: “That is the entire purpose. If the audit lived in the application, it would live in the one application that remembered to include it. This trigger runs for the ad hoc insert at midnight too. Auditing updates would need an UPDATE trigger of its own.”
The court: Both true. A trigger is invisible from the statement. That’s a feature when the rule must hold everywhere and a hazard when nobody documents it. The remedy is a comment in the table’s definition and a list of triggers in the runbook. Absence of either is negligence by the owner, not the trigger.
Exhibit D: the case for the defence
The defence: “I ask the court to consider a rule no constraint on the movement table can enforce. Stock may never go below zero. Each movement row is legal on its own. Three of them together are not.”
INSERT dbo.Movement (sku, delta) VALUES (1, -2), (1, -2), (1, -2); -- stock for sku 1 was 5. Three legal rows, illegal together.
“A CHECK constraint on a movement row sees that row. The application sees one screen. My client sees the whole statement.”
Msg 50001: Movement would take a sku below zero. Whole statement rolled back. Stock for sku 1 afterwards: 5
The prosecution: “The same could be done in a stored procedure.”
The defence: “Only if every caller uses the procedure. The trigger doesn’t ask.”
The prosecution: “There is a balance column, dbo.Stock.onhand. A CHECK on that column would refuse a negative balance without my client’s help.”
The defence: “It would, and it should be there. Something still has to add the movements up and write that balance. That is what my client does, for every caller, including the one nobody remembers.”
The verdict on SQL Server triggers
The court finds as follows.
On the charge of slowing inserts: guilty of a minor offence. 161 milliseconds per 100,000 rows over the alternative. The log cost belongs to the audit, whichever mechanism writes it.
On the charge of auditing the wrong number of rows: not guilty. The trigger did as instructed. The instruction assumed one row. Sentence suspended, on condition that every trigger in the schema is reviewed for a scalar assignment from inserted or deleted.
On the charge of acting without knowledge: guilty, with the owner named as co-defendant for failing to document it.
On the defence’s own charge: partly upheld. A CHECK on the balance column can refuse a negative balance, and belongs there. Something still has to maintain that balance across every caller, and for that the court knows of no better tool.
Sentencing guidelines, for your own schema
Use OUTPUT for plain audit when you control every write path. It’s faster and it’s visible in the statement.
Use a trigger for a rule that must hold across rows or across callers. Write it as a set. Document it on the table. Keep it short, because it runs inside every transaction that touches the table. Put a CHECK on any balance a trigger maintains, so the constraint catches what the trigger misses.
Never let a trigger call out to anything slow, and never let it hide a rollback the caller doesn’t expect.
If your schema has triggers nobody can explain, that review is a day’s work and I’ve done it many times.
A trigger is not slow, it is invisible, and invisible things get blamed for everything.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
