Audit created and modified by user in temporal table (without trigger)

Microsoft introduced Temporal tables in SQL Server 2016. Temporal tables are system versioned tables that have built-in support for row-level change tracking. We can create a new table as a temporal table or we can convert an existing table into a temporal table. The temporal tables keep the current data into the main table and move the historical data into an associated history table. The history table has the same schema as the main table and we cannot modify the schema of the history table. In order to enable the row versioning in a temporal table, we add two system period columns to mark row valid from and to date ranges. A temporal table facilitates type 2 SCD (slowly changing dimension) without writing any complex procedure or trigger. It is very handy and is very useful in case we need to capture row-level changes in a table. However, a temporal table enables auditing on all columns of the table and we cannot choose a few selective columns only.

Along with the existing columns of the temporal table, sometimes, we also need to capture the created and modified by user names in the history table. Of course, we can do this by creating a trigger on the temporal table. However, if the table accommodates a higher number of DMLs, the trigger-based approach may impact the performance of the DML operations on this table. In this post, we will learn how we can “Audit created and modified by user in temporal table without trigger“.

Capture Created and Modified by user names in the history table of the temporal table

Let’s create a temporal table using the below script. We will use this table to demonstrate the auditing of the CreatedBy and ModifiedBy user names in the history table without using a trigger.

USE TestDB
GO

CREATE TABLE dbo.EmpMaster
(
	EmployeeId INT NOT NULL PRIMARY KEY,
	Name NVARCHAR(256) NOT NULL,
	EmpAddress VARCHAR(256) NOT NULL,
	Contact VARCHAR(20) NOT NULL,
	Designation VARCHAR(256),
	DateOfBirth DATETIME,
	Gender CHAR(1) NOT NULL,
	CreatedBy NVARCHAR(128) NOT NULL DEFAULT (SUSER_SNAME()),	--Row created by user name
	LoggedInUser AS (SUSER_SNAME()),				--Current logged in user name
	StartTime DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL,
	EndTime DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL,
	PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmpMaster_History, DATA_CONSISTENCY_CHECK = ON))
GO

In this table, we have two columns “CreatedBy” and “LoggedInUser” to capture CreatedBy and ModifiedBy user names in the associated history table. Let’s discuss these columns in more detail:

CreatedBy Audit Column

CreatedBy NVARCHAR(128) NOT NULL DEFAULT (SUSER_SNAME()), --Row created by user name

The CreatedBy column is a regular column that has a default constraint using a built-in T-SQL function SUSER_NAME(). The function SUSER_NAME() is used to get the currently logged-in user name. In our case, when a user will perform an INSERT operation on this table, that user name will be stored in this column.

LoggedInUser Audit Column

LoggedInUser AS (SUSER_SNAME()), --Current logged in user name

The LoggedInUser column is a computed column that also uses the T-SQL built-in function SUSER_NAME(). So, it always displays the currently logged-in user name in all the rows of the table. It might look weird in the EmpMaster table. But, when a DML operation is performed on the EmpMaster table, this value gets logged into the history table (EmpMaster_History), and then it is used to get ModifiedBy user name.

Let’s add some dummy rows into the EmpMaster table to demonstrate this. Below is the INSERT script:

INSERT INTO dbo.EmpMaster 
(EmployeeId, Name, EmpAddress, Contact, Designation, DateOfBirth, Gender)
VALUES(100, 'Albert', 'New Delhi', '0123456789', 'Senior Software Enginner', '19850106', 'M'),
(101, 'Michael', 'Mumbai', '2103456789', 'Team Lead', '19800608', 'M'),
(102, 'Scott', 'Chennai', '6789012345', 'Project Manager', '19750709', 'M'),
(103, 'Smith', 'Kolkata', '1234567890', 'HR Manager', '19770810', 'M')
GO

The table looks like this:

Now, we are going to perform some DML operations on this table. However, before executing the below script, you can use different log-in credentials to distinguish between created by and modified by user names. Below is the script:

UPDATE dbo.EmpMaster SET Contact = '9876543210' WHERE EmployeeId = 100
GO
UPDATE dbo.EmpMaster SET Designation = 'Project Manager' WHERE EmployeeId = 101
GO

Now, let’s have a look at the history table:

We can see how we have logged the created and modified by details into the history table without using a trigger on the temporal table. We can extract these details from these tables using simple T-SQL queries now. If you want to get more details on how to query a temporal table, visit this link.

Thanks for the reading. Please share your inputs in the comment section.