What Is a Trigger?

Triggers are powerful for enforcing complex business rules, maintaining audit trails, and synchronizing data across tables. However, they execute silently and can be difficult to debug, so they should be used judiciously.

-- Simple AFTER trigger: audit trail
CREATE TRIGGER trg_AuditEmployeeChanges
ON employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO employee_audit (employee_id, change_type, changed_by, changed_date)
    SELECT
        i.employee_id,
        'UPDATE',
        SYSTEM_USER,
        GETDATE()
    FROM inserted i
    INNER JOIN deleted d ON i.employee_id = d.employee_id
    WHERE i.salary <> d.salary OR i.department_id <> d.department_id;
END;

INSERT Triggers

Fires when new rows are added to a table. The inserted virtual table contains the new rows.

CREATE TRIGGER trg_ValidateNewOrder
ON orders
AFTER INSERT
AS
BEGIN
    -- Reject orders with zero or negative quantity
    IF EXISTS (SELECT 1 FROM inserted WHERE quantity <= 0)
    BEGIN
        RAISERROR('Order quantity must be positive.', 16, 1);
        ROLLBACK;
        RETURN;
    END

    -- Auto-calculate total
    UPDATE o
    SET total = i.quantity * p.price
    FROM orders o
    INNER JOIN inserted i ON o.order_id = i.order_id
    INNER JOIN products p ON i.product_id = p.product_id;

    -- Log the insertion
    INSERT INTO order_log (order_id, action, action_date, performed_by)
    SELECT order_id, 'CREATED', GETDATE(), SYSTEM_USER
    FROM inserted;
END;

UPDATE Triggers

Fires when existing rows are modified. Both inserted (new values) and deleted (old values) virtual tables are available.

CREATE TRIGGER trg_PreventSalaryDecrease
ON employees
AFTER UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT 1
        FROM inserted i
        INNER JOIN deleted d ON i.employee_id = d.employee_id
        WHERE i.salary < d.salary
    )
    BEGIN
        RAISERROR('Salary decreases are not allowed. Use HR approval process.', 16, 1);
        ROLLBACK;
        RETURN;
    END

    -- Log salary increases
    INSERT INTO salary_history (employee_id, old_salary, new_salary, change_date, changed_by)
    SELECT
        i.employee_id,
        d.salary,
        i.salary,
        GETDATE(),
        SYSTEM_USER
    FROM inserted i
    INNER JOIN deleted d ON i.employee_id = d.employee_id
    WHERE i.salary > d.salary;
END;

DELETE Triggers

Fires when rows are removed. The deleted virtual table contains the rows that were removed.

CREATE TRIGGER trg_ArchiveDeletedOrders
ON orders
AFTER DELETE
AS
BEGIN
    -- Archive deleted orders
    INSERT INTO orders_archive (
        order_id, customer_id, order_date, total, status,
        deleted_by, deleted_date
    )
    SELECT
        d.order_id,
        d.customer_id,
        d.order_date,
        d.total,
        d.status,
        SYSTEM_USER,
        GETDATE()
    FROM deleted d;

    -- Log the deletion
    INSERT INTO audit_log (table_name, record_id, action, action_date, performed_by)
    SELECT 'orders', d.order_id, 'DELETED', GETDATE(), SYSTEM_USER
    FROM deleted d;
END;

INSTEAD OF Triggers

INSTEAD OF triggers are useful for views that are not directly updatable, or for intercepting and redirecting operations.

-- Make a view updatable using INSTEAD OF trigger
CREATE VIEW v_customer_orders AS
SELECT c.customer_id, c.name, c.email,
       o.order_id, o.order_date, o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

CREATE TRIGGER trg_UpdateCustomerOrders
ON v_customer_orders
INSTEAD OF UPDATE
AS
BEGIN
    -- Update customer info
    UPDATE customers
    SET name = i.name,
        email = i.email
    FROM customers c
    INNER JOIN inserted i ON c.customer_id = i.customer_id;

    -- Update order info
    UPDATE orders
    SET order_date = i.order_date,
        total = i.total
    FROM orders o
    INNER JOIN inserted i ON o.order_id = i.order_id
    WHERE i.order_id IS NOT NULL;
END;

Trigger Timing and Order

-- Control trigger execution order
CREATE TRIGGER trg_AuditFirst
ON employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO audit_log (message, log_time)
    VALUES ('Audit trigger fired', GETDATE());
END;

CREATE TRIGGER trg_NotifySecond
ON employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO notification_log (message, log_time)
    VALUES ('Notification trigger fired', GETDATE());
END;

-- Set execution order (SQL Server)
EXEC sp_settriggerorder @triggername = 'trg_AuditFirst',
                         @order = 'first',
                         @stmttype = 'UPDATE';

EXEC sp_settriggerorder @triggername = 'trg_NotifySecond',
                         @order = 'last',
                         @stmttype = 'UPDATE';

Disabled and Recursive Triggers

-- Disable a trigger
DISABLE TRIGGER trg_AuditEmployeeChanges ON employees;

-- Enable a trigger
ENABLE TRIGGER trg_AuditEmployeeChanges ON employees;

-- Disable all triggers on a table
DISABLE TRIGGER ALL ON employees;

-- Control recursive trigger behavior (SQL Server)
ALTER DATABASE YourDatabase SET RECURSIVE_TRIGGERS ON;

-- Recursive trigger example (use with caution)
CREATE TRIGGER trg_SetModifiedDate
ON employees
AFTER UPDATE
AS
BEGIN
    -- This fires the UPDATE, which fires the trigger again
    -- Only safe with proper recursion control
    UPDATE e
    SET modified_date = GETDATE()
    FROM employees e
    INNER JOIN inserted i ON e.employee_id = i.employee_id;
END;

Best Practices

-- Well-structured trigger with documentation
/*
    Trigger: trg_UpdateInventoryOnOrder
    Table: order_items
    Event: AFTER INSERT, UPDATE
    Purpose: Automatically decrements inventory stock when items are added to an order
    Author: DBA Team
    Created: 2024-01-15
*/
CREATE TRIGGER trg_UpdateInventoryOnOrder
ON order_items
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Decrease stock for inserted items
    UPDATE i
    SET i.stock = i.stock - ins.quantity
    FROM inventory i
    INNER JOIN inserted ins ON i.product_id = ins.product_id;

    -- Warn if stock went negative
    IF EXISTS (SELECT 1 FROM inventory WHERE stock < 0)
    BEGIN
        DECLARE @msg NVARCHAR(200) = 'Warning: inventory stock is negative for product(s)';
        RAISERROR(@msg, 10, 1); -- severity 10 = warning, does not rollback
    END
END;

Practical Example: Full Audit System

-- Create audit table
CREATE TABLE data_audit (
    audit_id INT IDENTITY(1,1) PRIMARY KEY,
    table_name NVARCHAR(128),
    record_id INT,
    action NVARCHAR(10),
    old_values NVARCHAR(MAX),
    new_values NVARCHAR(MAX),
    changed_by NVARCHAR(128),
    changed_date DATETIME2 DEFAULT GETDATE()
);

-- Generic audit trigger
CREATE TRIGGER trg_AuditEmployees
ON employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Log INSERTs
    INSERT INTO data_audit (table_name, record_id, action, new_values, changed_by)
    SELECT 'employees', employee_id, 'INSERT',
           (SELECT * FROM inserted i WHERE i.employee_id = ins.employee_id FOR JSON PATH),
           SYSTEM_USER
    FROM inserted ins;

    -- Log DELETEs
    INSERT INTO data_audit (table_name, record_id, action, old_values, changed_by)
    SELECT 'employees', employee_id, 'DELETE',
           (SELECT * FROM deleted d WHERE d.employee_id = del.employee_id FOR JSON PATH),
           SYSTEM_USER
    FROM deleted del;

    -- Log UPDATEs (only when values actually changed)
    INSERT INTO data_audit (table_name, record_id, action, old_values, new_values, changed_by)
    SELECT 'employees', i.employee_id, 'UPDATE',
           (SELECT * FROM deleted d WHERE d.employee_id = i.employee_id FOR JSON PATH),
           (SELECT * FROM inserted i2 WHERE i2.employee_id = i.employee_id FOR JSON PATH),
           SYSTEM_USER
    FROM inserted i
    INNER JOIN deleted d ON i.employee_id = d.employee_id
    WHERE i.name <> d.name
       OR i.salary <> d.salary
       OR i.department_id <> d.department_id;
END;