Triggers for Validation and Audit Logs – SQL Server | Lesson 23

Published / updated4 Oct 2026

Introduction

In Lesson 13, you met a rule that no constraint could enforce: marks must not be more than the paper's maximum. The maximum lives in ExamSubjects, another table, and a CHECK can only see its own row.

A trigger is code that SQL Server runs automatically when rows in a table change. In this lesson, you write two: one that refuses marks above the maximum, and one that records every change to a mark in an audit log.

By the end of this lesson, you will be able to:

Explain what a trigger is, and when it runs.
Use the inserted and deleted tables inside a trigger.
Write a trigger that refuses bad data with THROW.
Write an audit trigger that stores old and new values as JSON.
Write triggers that work for one row or 12,000 rows at once.
Decide between a constraint, a trigger, and a procedure for a rule.

Why this matters

A teacher types 105 instead of 15, a bulk import loads marks with a typo, or a support engineer runs a quick fix in SSMS.

All three skip the API's checks, but none of them can skip a trigger.

The second need is history. Lesson 14's audit columns say who changed a mark last; they do not say what it was before. When a parent disputes a report card, "it was 67, Anitha changed it to 69 on 12 August" settles the question.

The concept

Example: what a trigger is

A trigger is attached to one table and one or more actions: INSERT, UPDATE, DELETE. It runs inside the same transaction as the statement that fired it. If the trigger throws an error, the statement and its whole transaction are undone.

Think of a CCTV camera at the school gate. Nobody presses a button to start it; it records whoever passes. A trigger runs the same way, whoever changes the table.

Kind	Runs	SMS use
`AFTER` trigger	After the change, before the commit; can still undo it	Validate marks, write the audit log
`INSTEAD OF` trigger	Instead of the change	Make a complex view updatable; rarely needed

Example: the inserted and deleted tables

Inside a trigger, SQL Server gives you two temporary tables with the same columns as the real table:

Statement	`inserted` holds	`deleted` holds
`INSERT`	The new rows	Nothing
`UPDATE`	The rows after the change	The rows before the change
`DELETE`	Nothing	The removed rows

So "old value" is in deleted, and "new value" is in inserted. For an update, join the two on the primary key to see both side by side.

Example: one statement, many rows

A trigger fires once per statement, not once per row. An UPDATE that changes 12,000 marks fires the trigger once, with 12,000 rows in inserted.

The most common trigger bug is code like SELECT @Mark = MarksObtained FROM inserted, which keeps only one of those rows. Always treat inserted and deleted as tables: use EXISTS, joins, and set-based INSERT ... SELECT.

Example: which tool for which rule

Rule	Best tool	Why
Marks are zero or more	`CHECK`	One row, always true
Marks not above the paper's maximum	Trigger	Needs another table; must cover every path
A payment updates the invoice status	Procedure	A business action with several steps
Record every change to marks	Trigger	Must not depend on the caller remembering

Prefer constraints first, then procedures, and use triggers only for rules that must hold no matter who writes the data.

Apply it to the SMS

Save the script below as database\023-triggers.sql and run it. It creates the AuditLogs table and both triggers.

Show database/023-triggers.sql

database/023-triggers.sql
-- Lesson 23: triggers, and the AuditLogs table they write to.
-- Safe to run more than once.
USE SchoolDb;
GO

-- Old and new values of changed rows, stored as JSON text.
IF OBJECT_ID(N'dbo.AuditLogs', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.AuditLogs
    (
        Id        BIGINT IDENTITY(1, 1) NOT NULL,
        TableName VARCHAR(50)           NOT NULL,
        RecordId  BIGINT                NOT NULL,
        Action    VARCHAR(6)            NOT NULL,
        OldValues NVARCHAR(MAX)         NOT NULL,
        NewValues NVARCHAR(MAX)         NULL,  -- NULL for a delete
        ChangedAt DATETIME2(0)          NOT NULL CONSTRAINT DF_AuditLogs_ChangedAt DEFAULT (SYSUTCDATETIME()),
        ChangedBy INT                   NULL,
        CONSTRAINT PK_AuditLogs PRIMARY KEY (Id),
        CONSTRAINT CK_AuditLogs_Action CHECK (Action IN ('UPDATE', 'DELETE'))
    );
END;
GO

-- Rule from Lesson 13: marks can never be more than the paper's maximum.
-- A CHECK constraint cannot see ExamSubjects, so a trigger enforces it.
CREATE OR ALTER TRIGGER dbo.tr_Marks_ValidateMaxMarks
ON dbo.Marks
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (
        SELECT 1
        FROM inserted AS i
        INNER JOIN dbo.ExamSubjects AS es ON es.Id = i.ExamSubjectId
        WHERE i.MarksObtained > es.MaxMarks
    )
    BEGIN
        THROW 50001, 'Marks cannot be more than the maximum marks of the paper.', 1;
    END;
END;
GO

-- Every change to a mark is recorded with its old and new values.
CREATE OR ALTER TRIGGER dbo.tr_Marks_Audit
ON dbo.Marks
AFTER UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.AuditLogs (TableName, RecordId, Action, OldValues, NewValues, ChangedBy)
    SELECT
        'Marks',
        d.Id,
        CASE WHEN i.Id IS NULL THEN 'DELETE' ELSE 'UPDATE' END,
        (SELECT d.MarksObtained, d.IsAbsent FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES),
        CASE
            WHEN i.Id IS NULL THEN NULL
            ELSE (SELECT i.MarksObtained, i.IsAbsent FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)
        END,
        CAST(SESSION_CONTEXT(N'UserId') AS INT)  -- set by the API in Module 12
    FROM deleted AS d
    LEFT JOIN inserted AS i ON i.Id = d.Id;
END;
GO

-- Check: the triggers on Marks.
SELECT name AS TriggerName, is_disabled AS IsDisabled
FROM sys.triggers
WHERE parent_id = OBJECT_ID(N'dbo.Marks')
ORDER BY name;
GO

Example: the validation trigger

tr_Marks_ValidateMaxMarks joins every new or changed row in inserted to its paper in ExamSubjects. If any row is above the maximum, it throws error 50001:

Example
IF EXISTS (
    SELECT 1
    FROM inserted AS i
    INNER JOIN dbo.ExamSubjects AS es ON es.Id = i.ExamSubjectId
    WHERE i.MarksObtained > es.MaxMarks
)
BEGIN
    THROW 50001, 'Marks cannot be more than the maximum marks of the paper.', 1;
END;

Example: the audit trigger

tr_Marks_Audit writes one row to AuditLogs for every updated or deleted mark. It joins deleted (before) to inserted (after). For a delete, there is no row in inserted, so the action is DELETE and the new values are NULL.

FOR JSON PATH turns the old and new values into JSON text, such as {"MarksObtained":67.00,"IsAbsent":false}. The same audit table can then store changes from any table, whatever its columns.

SESSION_CONTEXT(N'UserId') reads a value the caller stored for this connection. In Module 12, the API stores the logged-in user's Id there, so the trigger knows who made the change without the API passing it to every statement.

Example: the triggers at work

The demo script tries both triggers inside transactions that are rolled back:

Show queries/023-triggers.sql

queries/023-triggers.sql
-- Lesson 23: triggers at work. Everything is rolled back, so no data changes.
USE SchoolDb;
GO

-- 1. The validation trigger refuses 105 marks on a paper out of 100.
BEGIN TRANSACTION;

UPDATE dbo.Marks
SET MarksObtained = 105
WHERE ExamSubjectId = 8
  AND EnrollmentId = 15;  -- Kabir, Unit Test 1 Mathematics

IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
GO

-- 2. A valid correction is saved, and the audit trigger records the old and new values.
BEGIN TRANSACTION;

EXEC sys.sp_set_session_context @key = N'UserId', @value = 3;  -- anitha.rao

UPDATE dbo.Marks
SET MarksObtained = 69
WHERE ExamSubjectId = 8
  AND EnrollmentId = 15;  -- Kabir: 67 corrected to 69

SELECT TableName, RecordId, Action, OldValues, NewValues, ChangedBy
FROM dbo.AuditLogs
WHERE TableName = 'Marks';

ROLLBACK TRANSACTION;
GO

Test 1, 105 marks for Kabir on a paper out of 100:

Output
Msg 50001, Level 16, State 1, Procedure tr_Marks_ValidateMaxMarks, Line 18
Marks cannot be more than the maximum marks of the paper.

Test 2, a valid correction from 67 to 69, made as user 3 (anitha.rao):

TableName	RecordId	Action	OldValues	NewValues	ChangedBy
Marks	5	UPDATE	`{"MarksObtained":67.00,"IsAbsent":false}`	`{"MarksObtained":69.00,"IsAbsent":false}`	3

After the script, Kabir's mark is still 67 and AuditLogs is empty again: both tests were rolled back, including the audit row.

Example: reading the audit log

JSON_VALUE reads one value out of JSON text, so the audit log can be read like normal columns:

Example
SELECT
    RecordId,
    JSON_VALUE(OldValues, '$.MarksObtained') AS OldMark,
    JSON_VALUE(NewValues, '$.MarksObtained') AS NewMark,
    ChangedBy
FROM dbo.AuditLogs
WHERE TableName = 'Marks'
ORDER BY ChangedAt DESC;

Run inside the Test 2 transaction, it returns one row: record 5, old mark 67.00, new mark 69.00, changed by user 3. '$.MarksObtained' is a JSON path: $ is the whole JSON object, and .MarksObtained is the value inside it.

Example: 12,000 rows at once

Updating every Class 1 mark in one statement changes 12,000 rows. The audit trigger fired once and wrote exactly 12,000 audit rows, in well under a second on a laptop. Set-based trigger code scales; row-by-row code would not.

Example: finding and switching triggers

Triggers are easy to forget because nobody calls them. To see every trigger on a table:

Example
SELECT name AS TriggerName, is_disabled AS IsDisabled
FROM sys.triggers
WHERE parent_id = OBJECT_ID(N'dbo.Marks');

In SSMS, expand the table and open its Triggers folder. DISABLE TRIGGER tr_Marks_Audit ON dbo.Marks; switches a trigger off and ENABLE TRIGGER switches it on again. Disabling is sometimes done during a one-time data migration; it must always be re-enabled in the same script, and checked afterwards.

Example: several triggers on one table

Marks has two AFTER triggers on update: validation and audit. SQL Server does not promise which one runs first. Here it does not matter: if validation throws, the whole statement is undone, including any audit row already written.

When order matters, keep the logic in one trigger instead of relying on order.

Example: how to test a trigger

Test every trigger with at least these four cases, each inside a rolled-back transaction:

One valid row: it is saved.
One invalid row: error 50001, nothing saved.
Many valid rows in one statement: all saved, and one audit row each.
Many rows where only one is invalid: error, and none saved.

Case 4 is the one people forget. Updating Aarav's mark to 105 and Diya's to 50 in one UPDATE fails with error 50001, and afterwards both marks are still 92 and 85. The statement is all or nothing, so the trigger never leaves half a change behind.

Common mistakes

Assuming one row in inserted. Multi-row statements lose data or skip checks. Fix: use EXISTS, joins, and INSERT ... SELECT.
Hidden business logic in triggers. Developers do not see why data changed. Fix: keep triggers small, named clearly, and listed in the reference.
Slow work inside a trigger. It runs inside every transaction on that table. Fix: no loops, no external calls; just set-based checks and inserts.
Disabling triggers for a bulk load and forgetting them. Bad data slips in. Fix: validate the loaded data, then re-enable in the same script.
Triggers that update their own table. They can fire themselves again. Fix: avoid it, or guard with TRIGGER_NESTLEVEL().

In real projects

From 16 years of .NET projects

Audit triggers have saved me in more disputes than any other database feature. When a school asked why a student's marks dropped after results were published, the audit log showed the exact change, the user, and the minute. The conversation ended there.

I have also removed many triggers. One system had a trigger that sent emails and called another database, so every save took seconds, and a network glitch made marks entry fail.

My rule: triggers check and record. They never call out, and they never surprise.

Best practices

Use a trigger only when a constraint cannot express the rule and every path must obey it.
Write all trigger code set-based, for any number of rows.
Keep triggers fast and side-effect free: no emails, files, or other servers.
Store old and new values in an audit table, with who and when.
Document every trigger next to the table it guards.
Test each trigger with one row, many rows, and a mix of valid and invalid rows.

Recap

A trigger runs automatically after (or instead of) a change, inside its transaction.
inserted holds new rows; deleted holds old rows.
A trigger fires once per statement, however many rows changed.
THROW in a trigger undoes the statement and its transaction.
tr_Marks_ValidateMaxMarks enforces Lesson 13's cross-table rule.
tr_Marks_Audit stores old and new values as JSON, with the user from SESSION_CONTEXT.
JSON_VALUE reads values back out of the audit log.

Homework

Library module task: Write a trigger on BookIssues that keeps BookCopies.Status correct: a new issue without a return date makes the copy Issued; setting ReturnedOn makes it Available. Never change a copy marked Lost.

A correct result looks like: after usp_IssueBook, the copy shows Issued; after a return, it shows Available.

Show the solution

library-assignment/023-library-trigger.sql
-- Library homework, Lesson 23: keep BookCopies.Status in step with BookIssues.
-- A new open issue makes the copy Issued; a return makes it Available again.
USE SchoolDb;
GO

CREATE OR ALTER TRIGGER dbo.tr_BookIssues_SyncCopyStatus
ON dbo.BookIssues
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE c
    SET Status = CASE WHEN i.ReturnedOn IS NULL THEN 'Issued' ELSE 'Available' END
    FROM dbo.BookCopies AS c
    INNER JOIN inserted AS i ON i.BookCopyId = c.Id
    WHERE c.Status <> 'Lost';
END;
GO

The UPDATE ... FROM ... JOIN inserted form updates every affected copy in one statement, so it works for one issue or a hundred.

Quiz

During an UPDATE, which table holds the rows as they were before the change?
1. inserted
2. deleted
3. AuditLogs
4. updated
Show answer
Answer: B. deleted deleted holds the old values; inserted holds the new ones.
An UPDATE changes 500 marks. How many times does an AFTER UPDATE trigger fire?
1. 500
2. Once
3. Twice
4. It depends on the index
Show answer
Answer: B. Once Triggers fire once per statement, with all 500 rows in inserted.
Why is 'marks not above MaxMarks' a trigger and not a CHECK constraint?
1. CHECK cannot compare numbers
2. MaxMarks is in another table
3. Triggers are faster
4. CHECK constraints are deprecated
Show answer
Answer: B. MaxMarks is in another table A CHECK sees only its own row; the trigger can join to ExamSubjects.
What happens when a trigger runs THROW?
1. Only the trigger stops
2. The statement and its transaction are rolled back
3. The row is saved with a warning
4. SQL Server restarts the trigger
Show answer
Answer: B. The statement and its transaction are rolled back A trigger runs inside the statement's transaction, so the error undoes it all.
Which of these should a trigger NOT do?
1. Check a rule across tables
2. Insert rows into an audit table
3. Send an email to the parent
4. Throw an error for bad data
Show answer
Answer: C. Send an email to the parent External calls make every save slow and fragile; do them outside the transaction.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)

F What is a trigger? concept

A trigger is code attached to a table that SQL Server runs automatically when rows are inserted, updated, or deleted. It runs inside the same transaction, so it can refuse a change with an error or record it in another table.

See the example · Permalink

F What are the inserted and deleted tables in a trigger? concept

They are temporary tables with the same columns as the real table. inserted holds new rows (after an insert or update), and deleted holds old rows (after an update or delete). For an update, joining them shows each row before and after.

See the example · Permalink

F Why does the SMS use a trigger for 'marks not above the maximum' instead of a CHECK constraint? concept

The maximum is in ExamSubjects, another table, and a CHECK constraint can only see the row being saved. The trigger joins inserted to ExamSubjects and throws error 50001 if any mark is too high.

See the example · Permalink

Mid (3-5 yrs)

M How many times does a trigger fire for an UPDATE that changes 500 rows? concept

Once. Triggers fire per statement, with all 500 rows in inserted and deleted. Code that assumes a single row, such as assigning a column to a variable, silently handles only one of them; trigger code must be set-based.

See the example · Permalink

M What happens when a trigger throws an error? concept

The statement that fired it fails, and its transaction is rolled back, including any rows the trigger itself wrote. In the SMS, updating two marks where one is 105 fails as a whole: both marks keep their old values.

See the example · Permalink

M How does the audit trigger know which user made a change? concept

The API stores the logged-in user's Id in SESSION_CONTEXT for the connection, with sp_set_session_context. The trigger reads it with SESSION_CONTEXT(N'UserId'), so it knows the user without the API passing it to every statement.

See the example · Permalink

Senior (6-9 yrs)

S Saving marks became slow after someone added a trigger. What do you check? scenario

Read the trigger: loops, cursors, queries without indexes, or calls to other databases or services run inside every save. Check the plan of the trigger's statements and the wait types during saves. Rewrite it set-based and move any external work out of the transaction.

How to answer: Show that triggers are part of the write path. Describe how you would measure (plans, duration with and without the trigger) and the usual culprits. End with a rule: triggers only check and record.

See the example · Permalink

S How would you choose between a constraint, a trigger, and a stored procedure for a business rule? design

Use a constraint when the rule concerns one row and is always true. Use a procedure for a multi-step business action. Use a trigger only when the rule needs other tables and must hold for every way data is written, such as imports and manual fixes.

How to answer: Give the decision order (constraint, then procedure, then trigger) and an SMS example for each. Mention the costs of triggers: hidden behaviour, write overhead, and testing effort.

See the example · Permalink

Architect / Lead (10+ yrs)

A How would you design auditing for a system that must keep every change for 7 years? design

Record old and new values with who and when for the tables that matter, using triggers, temporal tables, or change data capture. Store audit data so it cannot be edited by the application, partition or archive it by date, and plan its size, retention, and access.

How to answer: Compare the options: triggers (flexible, custom), system-versioned temporal tables (automatic history per table), and change data capture (log-based, for downstream use). Cover storage growth, query needs, security, and how long data must be kept.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-23-triggers (code repository coming soon)
Files: database/023-triggers.sql, queries/023-triggers.sql, library-assignment/023-library-trigger.sql
Video: coming soon

What's next

Triggers, procedures, and constraints all rely on transactions to undo work. Next, you control transactions yourself. Lesson 24: Transactions

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

Introduction​

Why this matters​

The concept​

Example: what a trigger is​

Example: the inserted and deleted tables​

Example: one statement, many rows​

Example: which tool for which rule​

Apply it to the SMS​

Example: the validation trigger​

Example: the audit trigger​

Example: the triggers at work​

Example: reading the audit log​

Example: 12,000 rows at once​

Example: finding and switching triggers​

Example: several triggers on one table​

Example: how to test a trigger​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: what a trigger is

Example: the inserted and deleted tables

Example: one statement, many rows

Example: which tool for which rule

Apply it to the SMS

Example: the validation trigger

Example: the audit trigger

Example: the triggers at work

Example: reading the audit log

Example: 12,000 rows at once

Example: finding and switching triggers

Example: several triggers on one table

Example: how to test a trigger

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next