Somebody asks who changed a row, and the table has no answer. Useful audit columns capture that answer from the first insert. They also need an update rule that every writer follows.
Decide What One Row Must Remember
I look for change timestamps before investigating an unexplained data correction. Without them, the conversation starts with guesses. A small set of columns gives the row a basic memory.
CreatedAt records when the row was inserted. UpdatedAt records its most recent change. CreatedBy and UpdatedBy identify the corresponding actors.
Those names don't define the complete policy. Decide whether an update that changes no business value counts. Decide whether imports preserve source creation times or use database insertion times.
UTC keeps stored timestamps comparable across application locations. SYSUTCDATETIME returns a datetime2 value for that purpose. Convert it to a business time zone when presenting it.
These columns hold the latest state, not every prior change. An overwritten UpdatedBy cannot tell you the previous actor. Keep that limitation visible from the beginning.
Give Audit Columns Useful Insert Defaults
Create the following table in a disposable database. The defaults give direct inserts a minimum audit record. Explicitly supplied values can still override defaults unless permissions and procedures prevent that.
The identity rule prefers a trusted session context value. ORIGINAL_LOGIN provides a fallback login name. Under connection pooling, that fallback usually names the shared application login.
An application user and a database login are different identities. Record the one your business investigation requires. Add a separate login column when both matter.
CREATE TABLE dbo.AuditColumnsDemo
(
ItemId int IDENTITY PRIMARY KEY,
ItemName nvarchar(100) NOT NULL,
CreatedAt datetime2(3) NOT NULL
CONSTRAINT DF_AuditColumnsDemo_CreatedAt DEFAULT SYSUTCDATETIME(),
UpdatedAt datetime2(3) NOT NULL
CONSTRAINT DF_AuditColumnsDemo_UpdatedAt DEFAULT SYSUTCDATETIME(),
CreatedBy nvarchar(128) NOT NULL
CONSTRAINT DF_AuditColumnsDemo_CreatedBy DEFAULT
(COALESCE(CONVERT(nvarchar(128), SESSION_CONTEXT(N'AppUser')), ORIGINAL_LOGIN())),
UpdatedBy nvarchar(128) NOT NULL
CONSTRAINT DF_AuditColumnsDemo_UpdatedBy DEFAULT
(COALESCE(CONVERT(nvarchar(128), SESSION_CONTEXT(N'AppUser')), ORIGINAL_LOGIN()))
);
INSERT dbo.AuditColumnsDemo(ItemName) VALUES(N'Sample item');
SELECT * FROM dbo.AuditColumnsDemo;Establish the Actor at a Trusted Boundary
A session context value isn't authentication. A caller able to submit arbitrary SQL can set a misleading value. Treat the application identity as trustworthy only through the established application boundary.
The application authenticates the person before setting AppUser. It must replace or clear that context when pooled connections change logical users. Test the behavior with the pooling configuration you use.
The next commands demonstrate setting and clearing a sample identity. They don't prove who submitted it. The distinction matters when audit columns support more than troubleshooting.
A read-only context key prevents later modification on that logical connection. It still doesn't authenticate the first value. It also changes how the application resets context, so review the full connection lifecycle.
EXEC sys.sp_set_session_context @key = N'AppUser', @value = N'sample-user';
INSERT dbo.AuditColumnsDemo(ItemName) VALUES(N'Application insert');
SELECT ItemId, CreatedBy, UpdatedBy FROM dbo.AuditColumnsDemo;
EXEC sys.sp_set_session_context @key = N'AppUser', @value = NULL;
Maintain Update Audit Columns Explicitly
A default fires for an insert when the column isn't supplied. It doesn't refresh UpdatedAt on every UPDATE. Leaving that assumption untested produces a creation timestamp wearing an update label.
Use a procedure rule when all application writes pass through procedures. Grant execution permission rather than direct table modification. Include audit updates in every supported write path.
The procedure preserves CreatedAt and CreatedBy. It updates the business value and latest change identity together. The statement therefore has one atomic row modification.
Use the procedure in the same disposable database. The definition begins a separate batch. A missing key produces a clear exception rather than a silent success.
GO
CREATE PROCEDURE dbo.UpdateAuditColumnsDemo
@ItemId int,
@ItemName nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.AuditColumnsDemo
SET ItemName = @ItemName,
UpdatedAt = SYSUTCDATETIME(),
UpdatedBy = COALESCE(CONVERT(nvarchar(128), SESSION_CONTEXT(N'AppUser')), ORIGINAL_LOGIN())
WHERE ItemId = @ItemId;
IF @@ROWCOUNT = 0
THROW 51000, 'The item does not exist.', 1;
END;
GO
EXEC dbo.UpdateAuditColumnsDemo @ItemId = 1, @ItemName = N'Revised item';
SELECT * FROM dbo.AuditColumnsDemo WHERE ItemId = 1;Use a Trigger When Writers Differ
A trigger is another option when several writers modify the table. It centralizes the update rule close to the data. Its implementation must handle every row in inserted, not one selected value.
An AFTER UPDATE trigger normally performs another update to stamp the changed rows. Review recursive trigger settings and guard against unwanted recursion. Include that extra write in performance testing.
A trigger doesn't repair an untrusted application identity. It can only read the execution context supplied to it. It also adds behavior that callers won't see in the original statement text.
I prefer the procedure rule when the permission model makes it complete. I accept a trigger when independent writers require a database-wide rule. The important part is coverage of every authorized writer.
Test Audit Columns on Forgotten Write Paths
A bulk import and a support correction deserve audit tests too. So does a scheduled procedure running through a service login. The ordinary screen update isn't the only way a row changes.
Check the permissions on direct inserts and updates. A caller with broad table permissions can supply misleading creation values. Separate business writers from administrators who maintain the database.
Can your latest-change identity distinguish two people sharing one application login? If the answer is no, don't describe it as a person-level audit. Improve the trusted application boundary first.
Test two changes inside the timestamp's precision. A datetime2(3) timestamp isn't a unique change identifier. Use another sequence or history key when ordering every change matters.
The clock also isn't a replacement for business effective dates. A correction entered today can apply to last month's transaction. Store that business date separately.
Choose History When the Question Needs It
Temporal tables preserve previous row versions through system-managed history. They help answer what the row looked like at a past time. They don't automatically identify the application actor.
Keep your actor columns when combining this design with temporal history. The previous version then preserves its recorded actor values too. Review retention and permissions on the history table.
I ask whether the team needs the latest change or the entire sequence. That answer separates basic audit columns from a history design. A last-change stamp cannot remember a conversation it never stored.
Audit columns work best when their limits are honest. Define the time, identity and write rules beside the schema. Then test them through each real entry point.
Audit names also deserve a length policy. Validate the application identity before setting its context value. Truncating two different identities into the same stored name weakens the evidence.
Review deletion separately. A removed row takes its current audit fields with it. Preserve a deletion record or history when the business must investigate that event.
Related reading on this blog: Temporal Tables: Keeping History Without a Trigger and Why You Should Name Default Constraints.
An audit timestamp is not a complete history, it is evidence of the latest recorded change.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
