Triggers and their trade-offs

Overview

Triggers are database modules that run automatically when a specific event occurs. In SQL Server, DML triggers respond to INSERT, UPDATE, or DELETE operations on a table or view. DDL triggers respond to schema-level events such as CREATE, ALTER, or DROP. Logon triggers respond to login events.

Triggers matter because they can enforce rules and record changes even when data is modified from many different applications, jobs, scripts, or admin tools. They can be useful for auditing, complex integrity checks, cross-table validation, and controlled behavior behind updatable views. They are also risky because they execute implicitly, can hide side effects, slow down writes, create recursion problems, and surprise maintainers.

For interviews, triggers are a classic trade-off topic. Strong candidates can explain AFTER vs INSTEAD OF, inserted and deleted pseudo-tables, multi-row trigger design, transaction behavior, why constraints are usually preferred for simple rules, and why triggers should be used sparingly and reviewed carefully.

Core Concepts

What A Trigger Is

A trigger is a special database module that automatically executes in response to an event.

Example DML trigger:

CREATE OR ALTER TRIGGER sales.trg_Orders_Audit
ON sales.Orders
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT audit.OrderAudit (OrderId, AuditAction, AuditAt)
    SELECT OrderId, 'INSERT', SYSUTCDATETIME()
    FROM inserted
    WHERE NOT EXISTS (SELECT 1 FROM deleted WHERE deleted.OrderId = inserted.OrderId);

    INSERT audit.OrderAudit (OrderId, AuditAction, AuditAt)
    SELECT OrderId, 'DELETE', SYSUTCDATETIME()
    FROM deleted
    WHERE NOT EXISTS (SELECT 1 FROM inserted WHERE inserted.OrderId = deleted.OrderId);

    INSERT audit.OrderAudit (OrderId, AuditAction, AuditAt)
    SELECT inserted.OrderId, 'UPDATE', SYSUTCDATETIME()
    FROM inserted
    JOIN deleted
        ON deleted.OrderId = inserted.OrderId;
END;

The trigger runs because a data modification happened, not because the application explicitly called it.

DML Triggers

DML triggers fire for data modifications:

• INSERT
• UPDATE
• DELETE

They can be created on tables or views depending on trigger type. They are often used for auditing, complex validation, derived writes, and protecting rules that cannot be expressed through normal constraints.

AFTER Triggers

An AFTER trigger runs after the triggering statement and after constraint checks for the operation have succeeded.

CREATE OR ALTER TRIGGER sales.trg_OrderLine_AfterInsert
ON sales.OrderLine
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE o
    SET UpdatedAt = SYSUTCDATETIME()
    FROM sales.Orders AS o
    JOIN inserted AS i
        ON i.OrderId = o.OrderId;
END;

Use AFTER triggers for work that should happen only after the base modification is accepted, such as audit rows or derived updates.

INSTEAD OF Triggers

An INSTEAD OF trigger runs instead of the triggering statement. It can be used to customize modifications against a view or intercept modifications before applying them.

CREATE OR ALTER TRIGGER sales.trg_vwOrderUpdate
ON sales.vwOrderEdit
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE o
    SET Status = i.Status
    FROM sales.Orders AS o
    JOIN inserted AS i
        ON i.OrderId = o.OrderId;
END;

This can make a view writable, but it also hides write behavior behind the view.

inserted And deleted Tables

DML triggers use logical tables called inserted and deleted.

• inserted contains new rows for INSERT and UPDATE.
• deleted contains old rows for DELETE and UPDATE.
• An UPDATE can be understood as old rows in deleted and new rows in inserted.

Triggers should use these tables set-wise, not as if they contain only one row.

Multi-Row Trigger Design

SQL Server triggers fire once per statement, not once per row. A single UPDATE statement can affect 10,000 rows and fire the trigger once with 10,000 rows in inserted and deleted.

Bad pattern:

DECLARE @OrderId int;

SELECT @OrderId = OrderId
FROM inserted;

This silently picks one row when multiple rows are present.

Better pattern:

UPDATE o
SET UpdatedAt = SYSUTCDATETIME()
FROM sales.Orders AS o
JOIN inserted AS i
    ON i.OrderId = o.OrderId;

Always write triggers to handle multi-row operations unless there is a clear and enforced reason that only one row can change.

Transaction Behavior

The triggering statement and trigger run in the same transaction. If the trigger raises an error and the transaction is rolled back, the original data modification is rolled back too.

This is powerful because it lets triggers enforce rules. It is also risky because slow trigger work keeps locks open longer and can increase blocking, deadlocks, and transaction log pressure.

Triggers Vs Constraints

Prefer declarative constraints when they can express the rule.

Use constraints for:

• Primary keys.
• Unique rules.
• Foreign keys.
• Not-null rules.
• Check constraints.
• Defaults.

Consider triggers only when the rule cannot be expressed cleanly as a constraint, such as complex cross-table validation or custom auditing.

Constraints are easier to discover, reason about, optimize, and validate.

Auditing With Triggers

Triggers are often used to write audit rows because they can capture modifications regardless of which application performed them.

Example:

CREATE OR ALTER TRIGGER sales.trg_Customer_AuditUpdate
ON sales.Customers
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT audit.CustomerChange
    (
        CustomerId,
        OldEmail,
        NewEmail,
        ChangedAt
    )
    SELECT
        d.CustomerId,
        d.Email,
        i.Email,
        SYSUTCDATETIME()
    FROM inserted AS i
    JOIN deleted AS d
        ON d.CustomerId = i.CustomerId
    WHERE ISNULL(i.Email, '') <> ISNULL(d.Email, '');
END;

For larger audit needs, also consider built-in features such as temporal tables, change data capture, or application-level audit events, depending on requirements.

Hidden Side Effects

Triggers are implicit. A developer may run a simple UPDATE but unknowingly cause extra writes, validations, calls, or errors through a trigger.

Hidden side effects can cause:

• Surprise performance overhead.
• Unexpected transaction rollbacks.
• Harder debugging.
• Migration failures.
• Replication or bulk-load issues.
• Complex dependency chains.

Good trigger usage requires clear naming, documentation, tests, and operational awareness.

Recursion And Nesting

Triggers can lead to nested or recursive behavior when trigger actions modify tables that fire other triggers. This can be intentional, but it can also cause loops, repeated work, or difficult debugging.

Design triggers to avoid unnecessary cascading behavior. If nesting or recursion is required, document it and test failure paths.

Performance Trade-Offs

Triggers add work to the original transaction.

Performance concerns include:

• Extra reads and writes.
• Longer lock duration.
• More transaction log usage.
• Blocking and deadlock risk.
• Slow audit tables.
• Row-by-row trigger logic.
• Trigger chains.
• Poor indexing on tables touched by the trigger.

A trigger that is fast for one-row updates may be dangerous for batch updates.

Security Risks

Triggers can run under elevated execution context. Malicious or careless trigger code can perform actions the original caller did not expect. Trigger definitions should be source-controlled, reviewed, and permissioned carefully.

Avoid using triggers as a place for broad privileged behavior that would be unsafe if called directly.

Common Mistakes

Common mistakes include:

• Assuming one row in inserted or deleted.
• Using triggers instead of simple constraints.
• Performing slow row-by-row cursor work inside triggers.
• Hiding business workflows in triggers.
• Creating recursive trigger chains accidentally.
• Forgetting triggers fire during bulk or maintenance operations.
• Writing audit triggers without indexing audit tables.
• Returning result sets from triggers.
• Ignoring error and rollback behavior.
• Failing to document trigger side effects.

Best Practices

Best practices include:

• Use triggers sparingly.
• Prefer constraints for declarative rules.
• Write set-based trigger logic.
• Always handle multi-row operations.
• Keep trigger work short and deterministic.
• Avoid external calls and long-running work.
• Document side effects.
• Source-control and review trigger definitions.
• Test inserts, updates, deletes, and batch operations.
• Monitor performance and blocking after deployment.