SQL Trigger – Complete Guide (Beginner to Practical)

What is a SQL Trigger?

A SQL Trigger is a special type of stored procedure that automatically runs (fires) when a specific event happens in a database table.

In simple terms:
“Trigger = Auto action when something changes in your table.”

Why Use Triggers?

Triggers are useful when you want automatic behavior without writing extra code in your application.

Common uses:

• Audit logs (track who changed what)
• Data validation
• Enforcing business rules
• Syncing data between tables
• Preventing invalid operations

Types of SQL Triggers

1. DML Triggers (Most Common)

Fire when data changes.

• INSERT → when new data is added
• UPDATE → when data is modified
• DELETE → when data is removed

2. DDL Triggers

Fire when database structure changes.

• CREATE, ALTER, DROP

3. LOGON Triggers (SQL Server)

• Fire when a user logs in.

Trigger Timing

1. AFTER Trigger

• Runs after the operation is completed.

2. INSTEAD OF Trigger

• Runs instead of the operation (used to override behavior, often with views).

Basic Syntax (SQL Server)

CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    -- Trigger logic here
END

Example 1: Audit Log (Real-World Use Case)

When a record is updated, store old and new values.

CREATE TRIGGER trg_AuditEmployee
ON Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO EmployeeAudit (EmployeeID, OldSalary, NewSalary, ChangedDate)
    SELECT 
        d.EmployeeID,
        d.Salary AS OldSalary,
        i.Salary AS NewSalary,
        GETDATE()
    FROM deleted d
    JOIN inserted i ON d.EmployeeID = i.EmployeeID;
END

Explanation:

• deleted → old data
• inserted → new data

Example 2: Prevent Delete

CREATE TRIGGER trg_PreventDelete
ON Employees
INSTEAD OF DELETE
AS
BEGIN
    PRINT 'Delete operation is not allowed!';
END

Example 3: Auto Update Timestamp

CREATE TRIGGER trg_UpdateTimestamp
ON Employees
AFTER UPDATE
AS
BEGIN
    UPDATE Employees
    SET UpdatedAt = GETDATE()
    WHERE EmployeeID IN (SELECT EmployeeID FROM inserted);
END

Advantages of Triggers

• Automatic execution (no manual call)
• Enforces rules at database level
• Improves data integrity
• Useful for auditing

Disadvantages of Triggers

• Hard to debug
• Can affect performance if overused
• Hidden logic (not visible in application code)
• Can cause recursive issues

Best Practices

• Keep trigger logic simple and fast
• Avoid heavy queries inside triggers
• Always handle multi-row operations
• Use triggers only when necessary (not for everything)
• Document your triggers clearly

When NOT to Use Triggers

• Complex business logic → use application layer
• High-performance systems with heavy writes
• When simpler constraints or procedures can do the job

Conclusion

A SQL Trigger is a powerful tool for automating actions inside your database. When used correctly, it helps maintain data consistency and enforce rules without extra application code. But overusing triggers can lead to performance and maintenance issues—so use them wisely.