29. Triggers & Audit Logging -- Automated Actions

Level: Advanced SQL Server for real projects

ℹ️ What You'll Learn

• Trigger syntax (CREATE TRIGGER)
• AFTER triggers (execute after INSERT/UPDATE/DELETE)
• INSTEAD OF triggers (replace action)
• INSERTED and DELETED pseudo-tables
• Audit logging with triggers
• Prevent invalid operations
• Trigger performance impact
• Common trigger mistakes
• Best practices

Triggers automate actions when data changes. Log every edit. Enforce rules. This article teaches automated database logic.

Why Triggers Matter for Backend Developers

Automated enforcement without backend code:

Requirement: "Log every student insert, update, delete
            Prevent exam result insert without valid student"
    v
Without trigger: Backend must handle logging and validation
WITH trigger:    Database handles automatically
    v
Consistent enforcement regardless of client
Audit trail automatic

Triggers enforce rules at database layer.

Trigger Syntax

CREATE TRIGGER TriggerName
ON TableName
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    -- Trigger logic
    -- Access INSERTED and DELETED pseudo-tables
END;

Key concepts:

• AFTER: Fires after INSERT/UPDATE/DELETE
• INSTEAD OF: Fires instead of action (can prevent it)
• INSERTED: Contains new values
• DELETED: Contains old values

Example 1: Audit Log Trigger (INSERT)

Log every new student:

CREATE TRIGGER trg_Student_Insert
ON Student
AFTER INSERT
AS
BEGIN
    INSERT INTO AuditLog (Action, EntityName, EntityId, OldValues, NewValues, ChangedBy, ChangedAt)
    SELECT 
        'INSERT',
        'Student',
        i.StudentId,
        NULL,
        CONCAT('Name=', i.Name, ', ClassName=', i.ClassName),
        SYSTEM_USER,
        GETDATE()
    FROM INSERTED i;
END;

-- When you insert student:
INSERT INTO Student (Name, ClassName, Status) VALUES ('Ravi Kumar', '10-A', 'Active');
-- Trigger automatically logs to AuditLog

INSERTED table: Contains new student row. Trigger logs it.

Example 2: Audit Log Trigger (UPDATE)

Track changes to student data:

CREATE TRIGGER trg_Student_Update
ON Student
AFTER UPDATE
AS
BEGIN
    INSERT INTO AuditLog (Action, EntityName, EntityId, OldValues, NewValues, ChangedBy, ChangedAt)
    SELECT 
        'UPDATE',
        'Student',
        d.StudentId,
        CONCAT('Name=', d.Name, ', ClassName=', d.ClassName),
        CONCAT('Name=', i.Name, ', ClassName=', i.ClassName),
        SYSTEM_USER,
        GETDATE()
    FROM DELETED d
    INNER JOIN INSERTED i ON d.StudentId = i.StudentId;
END;

-- When you update student:
UPDATE Student SET ClassName = '11-A' WHERE StudentId = 1;
-- Trigger logs: DELETED has old ClassName, INSERTED has new ClassName

DELETED: Old values. INSERTED: New values. Compare for audit.

Example 3: Prevent Invalid Operation (INSTEAD OF)

Prevent exam result insert without valid student:

CREATE TRIGGER trg_ExamResult_InsertValidation
ON ExamResult
INSTEAD OF INSERT
AS
BEGIN
    -- Check if student exists
    IF EXISTS (
        SELECT 1 FROM INSERTED i
        LEFT JOIN Student s ON i.StudentId = s.StudentId
        WHERE s.StudentId IS NULL
    )
    BEGIN
        RAISERROR('Invalid student ID. Exam result not inserted.', 16, 1);
        ROLLBACK;
        RETURN;
    END;
    
    -- If valid, insert
    INSERT INTO ExamResult (StudentId, ExamId, MarksObtained, IsAbsent)
    SELECT StudentId, ExamId, MarksObtained, IsAbsent FROM INSERTED;
END;

-- Try to insert with invalid student:
INSERT INTO ExamResult (StudentId, ExamId, MarksObtained) 
VALUES (999, 1, 85);
-- Trigger blocks: ERROR: Invalid student ID

INSTEAD OF: Replace action. Validate before allowing insert.

Example 4: Prevent Deletion (INSTEAD OF DELETE)

Prevent student deletion (soft delete instead):

CREATE TRIGGER trg_Student_DeletePrevent
ON Student
INSTEAD OF DELETE
AS
BEGIN
    -- Instead of deleting, mark as inactive
    UPDATE Student 
    SET Status = 'Inactive'
    WHERE StudentId IN (SELECT StudentId FROM DELETED);
    
    -- Log the action
    INSERT INTO AuditLog (Action, EntityName, EntityId, ChangedBy, ChangedAt)
    SELECT 
        'DELETE (soft)',
        'Student',
        d.StudentId,
        SYSTEM_USER,
        GETDATE()
    FROM DELETED d;
END;

-- Try to delete student:
DELETE FROM Student WHERE StudentId = 1;
-- Trigger: Sets Status = 'Inactive' instead of deleting
-- Row still in table (soft delete)

INSTEAD OF DELETE: Prevent permanent deletion. Soft delete instead.

Example 5: Prevent Duplicate Exam Results

Block inserting same exam twice:

CREATE TRIGGER trg_ExamResult_NoDuplicate
ON ExamResult
INSTEAD OF INSERT
AS
BEGIN
    -- Check for existing result
    IF EXISTS (
        SELECT 1 FROM INSERTED i
        WHERE EXISTS (
            SELECT 1 FROM ExamResult er
            WHERE er.StudentId = i.StudentId 
            AND er.ExamId = i.ExamId
        )
    )
    BEGIN
        RAISERROR('Exam result already exists for this student.', 16, 1);
        ROLLBACK;
        RETURN;
    END;
    
    -- Insert valid results
    INSERT INTO ExamResult (StudentId, ExamId, MarksObtained, IsAbsent)
    SELECT StudentId, ExamId, MarksObtained, IsAbsent FROM INSERTED;
END;

Prevents duplicate insertions at database level.

Example 6: Auto-Update Timestamp

Automatically update LastModified:

CREATE TRIGGER trg_Student_UpdateTimestamp
ON Student
AFTER UPDATE
AS
BEGIN
    UPDATE Student
    SET LastModified = GETDATE()
    WHERE StudentId IN (SELECT StudentId FROM INSERTED);
END;

-- Update student:
UPDATE Student SET Name = 'Ravi Kumar Updated' WHERE StudentId = 1;
-- Trigger: Automatically sets LastModified = GETDATE()

Trigger adds timestamp without backend code.

Example 7: Cascading Updates (Trigger vs FK)

Sync class count when student added/removed:

CREATE TRIGGER trg_Student_UpdateClassCount
ON Student
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    -- Update class counts
    UPDATE Class
    SET StudentCount = (
        SELECT COUNT(*) FROM Student 
        WHERE ClassName = Class.ClassName
    )
    WHERE ClassName IN (
        SELECT DISTINCT ClassName FROM INSERTED
        UNION
        SELECT DISTINCT ClassName FROM DELETED
    );
END;

-- When student added:
INSERT INTO Student (Name, ClassName) VALUES ('New Student', '10-A');
-- Trigger: Updates Class.StudentCount for 10-A

Trigger maintains denormalized data (count field).

---

Common Trigger Mistakes

Mistake 1: Trigger causing infinite loop

Wrong:

CREATE TRIGGER trg_Student_Update
ON Student
AFTER UPDATE
AS
BEGIN
    UPDATE Student SET LastModified = GETDATE();
    -- Trigger fires on UPDATE
    -- Trigger UPDATE fires trigger again (infinite loop)
END;

Result: Recursion, performance crash.

Fix: Add recursion check:

IF @@NESTLEVEL > 1
    RETURN;

UPDATE Student SET LastModified = GETDATE();

Or use DISABLED trigger and manual update.

Mistake 2: Trigger using cursor (slow)

Wrong:

CREATE TRIGGER trg_ExamResult_Insert
ON ExamResult
AFTER INSERT
AS
BEGIN
    DECLARE c CURSOR FOR SELECT StudentId FROM INSERTED;
    OPEN c;
    FETCH NEXT FROM c INTO @StudentId;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Process each row (cursor = slow)
        FETCH NEXT FROM c INTO @StudentId;
    END;
    CLOSE c;
END;
-- Slow for bulk inserts

Fix: Use set-based logic:

CREATE TRIGGER trg_ExamResult_Insert
ON ExamResult
AFTER INSERT
AS
BEGIN
    -- Set-based: All rows at once (fast)
    INSERT INTO AuditLog (...)
    SELECT ... FROM INSERTED;
END;

Mistake 3: INSTEAD OF with row-by-row logic

Wrong:

CREATE TRIGGER trg_Student_Insert
INSTEAD OF INSERT
AS
BEGIN
    -- Doesn't handle multiple inserts well
    INSERT INTO Student SELECT * FROM INSERTED;
    UPDATE CLASS SET Count = Count + @@ROWCOUNT;
    -- @@ROWCOUNT applies to last statement (UPDATE, not INSERT)
END;

Fix: Reference INSERTED properly:

INSERT INTO Student SELECT * FROM INSERTED;

UPDATE Class
SET StudentCount = StudentCount + (SELECT COUNT(*) FROM INSERTED)
WHERE ClassName IN (SELECT DISTINCT ClassName FROM INSERTED);

Mistake 4: Trigger without error handling

Wrong:

CREATE TRIGGER trg_ExamResult_Insert
INSTEAD OF INSERT
AS
BEGIN
    -- No error handling
    IF (SELECT COUNT(*) FROM INSERTED) > 100
        RAISERROR('Too many inserts', 16, 1);
    -- But still inserts rows if no error
END;

Fix: ROLLBACK on error:

BEGIN TRY
    IF (SELECT COUNT(*) FROM INSERTED) > 100
    BEGIN
        THROW 50001, 'Too many inserts', 1;
    END;
    INSERT INTO ...;
END TRY
BEGIN CATCH
    ROLLBACK;
    THROW;
END CATCH

Mistake 5: Performance impact (trigger on every operation)

Wrong:

-- Triggers on every INSERT, UPDATE, DELETE
CREATE TRIGGER trg1 ON Student AFTER INSERT ...;
CREATE TRIGGER trg2 ON Student AFTER UPDATE ...;
CREATE TRIGGER trg3 ON Student AFTER DELETE ...;
CREATE TRIGGER trg4 ON Student AFTER INSERT ...;
-- 4 triggers x bulk insert = slow

Result: Bulk operations slow down.

Fix: Combine logic:

CREATE TRIGGER trg_Student_All
ON Student
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    IF EXISTS (SELECT 1 FROM INSERTED)
        -- Handle insert/update
    IF EXISTS (SELECT 1 FROM DELETED)
        -- Handle delete
END;

---

Best Practices for Triggers

1. Use set-based logic -- Not cursors
2. Keep triggers simple -- One responsibility
3. Handle bulk operations -- INSERTED/DELETED can have many rows
4. Avoid recursive triggers -- Check @@NESTLEVEL
5. Error handling required -- ROLLBACK on validation failure
6. Document purpose -- Why trigger exists
7. Test performance -- Bulk operations especially
8. Use instead of cascading in code -- Centralize in database

---

🎯 Q1: What's the difference between AFTER and INSTEAD OF?

AFTER: Fires after INSERT/UPDATE/DELETE (action completed).

AFTER INSERT: Student inserted, then trigger runs

INSTEAD OF: Fires instead of action (replaces it).

INSTEAD OF DELETE: Trigger runs, original DELETE prevented

AFTER: Log after change. INSTEAD OF: Validate/prevent change.

🎯 Q2: What are INSERTED and DELETED tables?

INSERTED: New values (INSERT, UPDATE).

INSERT into Student: INSERTED contains new row

DELETED: Old values (UPDATE, DELETE).

UPDATE: DELETED has old data, INSERTED has new
DELETE: DELETED has deleted row

Compare to audit changes.

🎯 Q3: How do I prevent recursion in triggers?

Check @@NESTLEVEL:

CREATE TRIGGER trg_Student_Update
ON Student
AFTER UPDATE
AS
BEGIN
    IF @@NESTLEVEL > 1
        RETURN;  -- Prevent recursion
    
    -- Trigger logic
END;

Or disable recursive triggers in server settings.

🎯 Q4: Can I call a stored procedure from a trigger?

Yes:

CREATE TRIGGER trg_Student_Insert
ON Student
AFTER INSERT
AS
BEGIN
    EXEC sp_LogStudentInsert;  -- Call procedure
END;

Keep procedure simple, avoid nesting.

🎯 Q5: Should I use triggers or application logic?

Triggers for:

• Audit logging (automatic)
• Enforcing rules (consistent)
• Preventing invalid operations

Application for:

• Complex business logic
• Multiple tables
• User-friendly error messages

Rule: Database enforces integrity (triggers). Application handles workflow (code).

🎯 Q6: How do I disable or drop a trigger?

Disable (keep trigger):

DISABLE TRIGGER trg_Student_Insert ON Student;

Enable:

ENABLE TRIGGER trg_Student_Insert ON Student;

Drop (delete):

DROP TRIGGER trg_Student_Insert;

Use DISABLE for testing.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Triggers and audit logging in SQL Server. Try these prompts:

• "Show trigger to log every INSERT into Student table"
• "How do I prevent duplicate exam results using INSTEAD OF?"
• "Explain INSERTED and DELETED pseudo-tables with UPDATE example"
• "How do I perform soft delete (prevent permanent deletion)?"
• "Quiz me: ask 5 questions about triggers, AFTER/INSTEAD OF, audit logging"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

30. Views & Materialized Views -- Simplified Queries ->