AFTER Triggers and the inserted/deleted Tables
An AFTER trigger (also called FOR trigger) fires once per triggering statement, after the INSERT, UPDATE, or DELETE has already been applied within the same transaction, and can only be defined on tables, not views. Inside the trigger body, SQL Server exposes two special pseudo-tables — inserted, containing the new row versions for INSERT/UPDATE, and deleted, containing the old row versions for UPDATE/DELETE — and because a trigger fires once per statement rather than once per row, trigger logic must be written to handle multiple affected rows at once, never assuming a single-row context.
Cricket analogy: The inserted/deleted tables are like the DRS review system comparing the ball-tracking 'before' and 'after' trajectory data simultaneously for every delivery under review in one go, not one frame at a time.
INSTEAD OF Triggers
An INSTEAD OF trigger replaces the triggering DML statement entirely rather than running after it, which makes it the mechanism for making an otherwise non-updatable view (for example, one with a JOIN across multiple base tables) support INSERT, UPDATE, or DELETE, by writing explicit logic in the trigger that applies the change to the correct underlying tables. Because the trigger substitutes for the original statement, if you actually want the original DML to also happen against the base table you must issue it explicitly inside the trigger body — nothing happens automatically.
Cricket analogy: An INSTEAD OF trigger is like a third umpire overturning the on-field call entirely and substituting their own ruling, rather than merely logging a note after the original decision stood.
CREATE OR ALTER TRIGGER trg_Orders_AuditUpdate
ON dbo.Orders
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.OrderAudit (OrderId, OldStatus, NewStatus, ChangedAt)
SELECT i.OrderId, d.Status, i.Status, SYSUTCDATETIME()
FROM inserted i
JOIN deleted d ON d.OrderId = i.OrderId
WHERE i.Status <> d.Status;
END;
GO
CREATE OR ALTER TRIGGER trg_CustomerOrderView_Insert
ON dbo.vw_CustomerOrders
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.Orders (CustomerId, OrderDate, Status)
SELECT CustomerId, OrderDate, 'Pending' FROM inserted;
END;Nested and Recursive Triggers, Performance
Triggers run inside the same implicit transaction as the statement that fired them, so a ROLLBACK inside a trigger rolls back the whole originating statement, and an unhandled error can abort the batch; by default, nested triggers are allowed (one trigger's DML firing another table's trigger) up to 32 levels, but recursive triggers (a trigger causing another firing of itself, directly or via a chain) are disabled unless explicitly enabled at the database level. Because every AFTER trigger adds overhead to every affected DML statement, heavy logic — especially anything doing row-by-row processing via a cursor inside a trigger — can significantly slow down high-volume tables and should be scrutinized during performance reviews.
Cricket analogy: A trigger causing a ROLLBACK is like an umpire calling a no-ball that voids the entire delivery's outcome, wickets and runs included, not just flagging an issue after the fact.
Triggers execute for the whole statement, not per row. If your logic assumes exactly one row is present in inserted or deleted, a multi-row UPDATE or a bulk INSERT will silently produce wrong results — always write trigger logic as a set-based operation joining against inserted/deleted.
Avoid putting expensive logic — especially cursors, calls to external services, or complex business rules — directly inside AFTER triggers on high-write tables. Every DML statement against that table pays the trigger's cost, and because it runs inside the same transaction, a slow trigger extends lock duration and can cause blocking across the whole system.
- AFTER triggers fire once per statement after the DML is applied and can only be defined on tables.
- The inserted and deleted pseudo-tables expose new and old row versions respectively, and can contain multiple rows.
- INSTEAD OF triggers replace the original DML statement, commonly used to make multi-table views updatable.
- A ROLLBACK inside a trigger rolls back the entire originating transaction and statement.
- Nested triggers are allowed by default (up to 32 levels); recursive triggers must be explicitly enabled.
- Trigger logic must be set-based, never assuming a single affected row.
- Heavy logic inside triggers, especially cursors, can significantly slow down high-volume DML.
Practice what you learned
1. How many times does an AFTER trigger fire for a single UPDATE statement affecting 500 rows?
2. What is the primary use case for an INSTEAD OF trigger?
3. What happens if you issue ROLLBACK inside an AFTER trigger?
4. Are recursive triggers enabled by default in SQL Server?
5. What do the inserted and deleted pseudo-tables represent in an AFTER UPDATE trigger?
Was this page helpful?
You May Also Like
Stored Procedures
Understand how to create, parameterize, and execute stored procedures to encapsulate reusable, precompiled T-SQL logic on the server.
Error Handling with TRY/CATCH
Learn how TRY...CATCH blocks intercept T-SQL runtime errors, how to inspect them with the ERROR_ functions, and how to combine them safely with transactions.
Variables and Control Flow
Learn how T-SQL variables store scalar values inside a batch and how IF/ELSE and WHILE let you branch and loop through procedural logic.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics