7 questions found
What is a trigger in SQL Server, and what kinds of events can cause one to run automatically?
Beginner
A trigger is a special kind of stored procedure that automatically runs in response to a specific event, such as an INSERT, UPDATE, or DELETE happening on a table, or certain data definition events like creating a new table, letting you automatically enforce rules or perform additional actions whenever that event occurs.
CREATE TRIGGER trg_LogOrderChanges
ON Orders
AFTER UPDATE
AS
BEGIN
INSERT INTO OrderAuditLog (OrderId, ChangedDate)
SELECT OrderId, GETDATE() FROM inserted;
END;
Real-world example
An order management system automatically logs every time an order is updated using a trigger, capturing a complete audit history without requiring the application code to remember to log each change manually.
Common follow-ups: What is the difference between the inserted and deleted pseudo tables available inside a trigger?;Can a single table have multiple triggers for the same event?
Change Data Capture (CDC);Error Handling with TRY CATCH
What is the difference between an AFTER trigger and an INSTEAD OF trigger?
Beginner
An AFTER trigger runs once the triggering action, like an insert or update, has already completed, letting you perform additional actions based on the change that just happened, while an INSTEAD OF trigger completely replaces the original triggering action, giving you full control to perform custom logic instead of the default behavior that would have otherwise occurred.
CREATE TRIGGER trg_PreventDelete
ON Orders
INSTEAD OF DELETE
AS
BEGIN
UPDATE Orders SET IsDeleted = 1
WHERE OrderId IN (SELECT OrderId FROM deleted);
END;
Real-world example
An order system uses an INSTEAD OF DELETE trigger to implement soft deletes, marking orders as deleted with a flag instead of actually removing them from the table, preserving the historical record while still appearing deleted to the application.
Common follow-ups: Why would a team choose soft deletes over actually removing rows from a table?;Can an INSTEAD OF trigger be created on a view as well as a table?
Data Types & Schema Design;Views
How do you access the old and new values of a row that was just updated from inside a trigger?
Intermediate
Inside a trigger, SQL Server automatically provides two special pseudo tables, called inserted and deleted, where the deleted table contains the row's values before the change and the inserted table contains its values after the change, letting you compare exactly what was modified during an update.
CREATE TRIGGER trg_PriceChangeLog
ON Products
AFTER UPDATE
AS
BEGIN
INSERT INTO PriceHistory (ProductId, OldPrice, NewPrice)
SELECT i.ProductId, d.Price, i.Price
FROM inserted i
JOIN deleted d ON i.ProductId = d.ProductId
WHERE i.Price <> d.Price;
END;
Real-world example
A product catalog automatically logs a complete price change history by comparing the old and new price values available through the inserted and deleted pseudo tables inside an update trigger.
Common follow-ups: What do the inserted and deleted tables contain during a plain INSERT or DELETE trigger?;Can multiple rows be present in inserted and deleted at the same time?
Change Data Capture (CDC);Data Types & Schema Design
Why should triggers generally be designed to handle multiple affected rows at once rather than assuming only a single row was changed?
Intermediate
A single statement, such as an UPDATE with a broad WHERE clause or none at all, can affect many rows simultaneously, and a trigger fires only once for that entire statement regardless of how many rows were changed, so if your trigger logic assumes only one row exists in the inserted or deleted tables, it will behave incorrectly and potentially cause serious bugs when a multi row operation actually occurs.
-- Correctly handles multiple rows using a set based approach
CREATE TRIGGER trg_UpdateInventory
ON OrderItems
AFTER INSERT
AS
BEGIN
UPDATE p
SET p.Stock = p.Stock - i.Quantity
FROM Products p
JOIN inserted i ON p.ProductId = i.ProductId;
END;
Real-world example
A developer fixes a buggy trigger that had assumed only one row would ever be inserted at a time, causing it to silently fail to update inventory correctly during a legitimate bulk order import that inserted hundreds of rows at once.
Common follow-ups: How do you test a trigger to confirm it correctly handles a multi row operation?;What common mistakes lead to triggers that only work correctly for single row changes?
Data Types & Schema Design;Error Handling with TRY CATCH
What are the performance implications of using triggers heavily on a busy, high transaction volume table?
Advanced
Triggers execute synchronously as part of the same transaction as the original statement, meaning any additional work performed inside a trigger directly adds to the time that transaction takes to complete and the duration locks are held, so a poorly optimized or overly complex trigger on a very busy table can become a significant performance bottleneck across the entire application.
-- A trigger performing a slow, unindexed lookup
-- will slow down every single insert on this table
CREATE TRIGGER trg_SlowCheck
ON Orders
AFTER INSERT
AS
BEGIN
IF EXISTS (SELECT 1 FROM VeryLargeTable WHERE UnindexedColumn = 'value')
PRINT 'Found';
END;
Real-world example
A team notices insert performance on their orders table has degraded significantly since adding a trigger, discovering it was performing an unindexed lookup against a large unrelated table, and fixes it by properly indexing that lookup.
Common follow-ups: How do you measure the specific performance impact a trigger is adding to a table's write operations?;What alternatives to triggers might be considered for less time sensitive additional processing?
Query Optimization & Plans;Change Data Capture (CDC)
How do nested and recursive triggers work in SQL Server, and what precautions should be taken to avoid unexpected behavior or infinite loops?
Advanced
A trigger can cause another trigger to fire, called nesting, and a trigger can even cause itself to fire again indirectly, called recursion, both of which are controlled by server level configuration settings, and without careful design, these situations can lead to infinite loops or deeply nested, hard to debug chains of triggers firing across multiple tables.
-- Check current nested trigger settings
SELECT SERVERPROPERTY('NestedTriggers');
-- Recursive triggers must be explicitly enabled
ALTER DATABASE MyDatabase SET RECURSIVE_TRIGGERS ON;
Real-world example
A team debugging an unexpected infinite loop discovers a trigger on one table was updating a second table, which in turn had its own trigger updating back to the first table, creating an unintended recursive chain that needed to be redesigned.
Common follow-ups: How do you safely test for potential infinite trigger loops before deploying to production?;What is a reasonable maximum nesting level SQL Server allows for triggers?
Error Handling with TRY CATCH;Query Optimization & Plans
What are some alternatives to using a trigger for tasks like auditing or enforcing business rules, and when might those alternatives be preferable?
Intermediate
Alternatives include using Change Data Capture for asynchronous change tracking with lower overhead, enforcing simple business rules directly through check constraints which are generally faster and simpler than a trigger, or handling more complex logic explicitly within application code or a stored procedure, which can be easier to test, debug, and maintain compared to logic hidden inside a trigger that might not be immediately obvious to other developers.
-- A check constraint is often simpler and faster
-- than a trigger for basic validation rules
ALTER TABLE Products ADD CONSTRAINT CK_PositivePrice CHECK (Price > 0);
Real-world example
A team replaces a trigger that was only checking for a simple positive price rule with a much simpler and faster check constraint, reserving triggers only for genuinely complex logic that truly requires them.
Common follow-ups: When is a trigger genuinely the right tool compared to these alternatives?;How do you decide between application level validation and database level enforcement?
Constraints (Primary Key
Foreign Key
Check & Unique);Change Data Capture (CDC)