SQL Server Triggers

A Trigger is a special type of stored procedure that automatically runs when an event occurs in the database server. They are most commonly used for auditing changes or enforcing complex business rules that simple constraints can't handle.

1. INSERTED and DELETED Tables

Triggers have access to two virtual tables: Inserted and Deleted. When you update a row, the old value goes to Deleted and the new value goes to Inserted. This is how you can track exactly who changed what and when.

2. The Hidden Logic Danger

Triggers run inside the same transaction as your main query. If a trigger is slow, your INSERT becomes slow. If a trigger fails, your main query fails. Because triggers are "Hidden," developers often spend hours debugging a query only to find that a trigger was causing the problem. Architect Rule: Use triggers sparingly!

CREATE TRIGGER trg_AuditUserChange
ON Users AFTER UPDATE AS
BEGIN
    INSERT INTO AuditLog (OldEmail, NewEmail, ChangedBy)
    SELECT D.Email, I.Email, SUSER_NAME()
    FROM Deleted D JOIN Inserted I ON D.Id = I.Id
END

4. Interview Mastery

Q: "What is the difference between an AFTER trigger and an INSTEAD OF trigger?"

Architect Answer: "An **AFTER** trigger runs after the primary action (Insert/Update/Delete) has succeeded but before the transaction is committed. An **INSTEAD OF** trigger intercepts the call and runs *its* code instead of the original SQL. `INSTEAD OF` triggers are extremely useful for making **Views** updatable—when you try to update a complex View, the trigger catches the call and manually updates the underlying base tables for you."