How to Log record changes in SQL Server in an Audit Table

In this post, we will explore how to audit table changes in SQL Server using a DDL trigger.Our objective is to monitor and record all table changes occurring across all databases within a specified SQL Server instance.

How to keep an history of changes to the database table?

For instance, when someone creates a new table, we aim to capture comprehensive information such as the name of the database where the table is created, as well as the table name itself.

To achieve this, I have established an auditable table designed to store the audit information.

Table Sql Script

/****** Object: Table [dbo].[AuditTable] Script Date: 10/21/2020 12:21:58 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AuditTable](
[DatabaseName] [varchar](300) NULL,
[TableName] [varchar](300) NULL,
[EventType] [varchar](300) NULL,
[LoginName] [varchar](300) NULL,
[UserName] [varchar](300) NULL,
[SQLCommand] [varchar](5000) NULL,
[ChangeDateTime] [datetime] NULL
) ON [PRIMARY]
GO

To accomplish this, we will utilize the EVENTDATA() function. This function provides us with data associated with the DDL event triggered. For instance, when we create a table, the corresponding DDL event is 'created_table'.

The EVENTDATA() function returns this data in XML format, structured as follows:

• eventType: Specifies the type of event (e.g., create table, alter table, drop table).
• loginName: Indicates the login name associated with the person who made the change.
• sqlCommand: Provides the exact Transact-SQL command executed.
• dateTime: Represents the date and time when the change occurred."

<EVENT_INSTANCE>
<EventType>CREATE_TABLE</EventType>
<PostTime>2020-10-21T11:47:48.820</PostTime>
<SPID>56</SPID>
<ServerName>ADEQUATE-ASHOK\SQLEXPRESS01</ServerName>
<LoginName>adk</LoginName>
<UserName>dbo</UserName>
<DatabaseName>DemoDB</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>tbltest</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>create table tbltest(Id int)</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>

Here, I've implemented a trigger named 'tr_Geteventdataproperties'. This trigger is scoped at the server level and will be activated in response to events such as create table, alter table, and drop table.

CREATE TRIGGER tr_Geteventdataproperties
ON ALL SERVER
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
select EVENTDATA()
END

Now, when we create a table, this trigger will be activated, returning the data associated with the 'create_table' event in XML format.

Let's observe this in action. Here, we have a create table statement. Upon execution, you'll notice that we receive the data from EVENTDATA() in XML format.

And when I click on that, notice that we get the event data.

<EVENT_INSTANCE>
<EventType>CREATE_TABLE</EventType>
<PostTime>2020-10-21T11:47:48.820</PostTime>
<SPID>56</SPID>
<ServerName>ADEQUATE-ASHOK\SQLEXPRESS01</ServerName>
<LoginName>adk</LoginName>
<UserName>dbo</UserName>
<DatabaseName>DemoDB</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>tbltest</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>create table tbltest(Id int)</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>

First, we have the EventType - 'create table' represents the EventType. PostTime indicates the timestamp of the event, followed by the server process ID, server name, login name, username, database name, schema name, object name (our table name), object type, and the exact Transact-SQL command that was executed.

Now, we can utilize this retrieved XML data to extract whichever pieces of information we require and store them in the AuditTable.

Next, we need to write a trigger to read this XML information and then insert it into our AuditTable.

CREATE TRIGGER tr_trackchangestoatable
ON ALL SERVER
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
DECLARE @EventData XML
SELECT @EventData = EVENTDATA()

INSERT INTO DemoDB.dbo.AuditTable
(DatabaseName, TableName, EventType, LoginName,UserName,
SQLCommand, ChangeDateTime)
VALUES
(
@EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(300)'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(300)'),
@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(300)'),
@EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(300)'),
@EventData.value('(/EVENT_INSTANCE/UserName)[1]', 'varchar(300)'),
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(5000)'),
GetDate()
)
END

The trigger named tr_trackchangestoatable operates at the server scope and is configured to respond to three events.

Examining the trigger's body, we initialize a variable of type XML named @EventData. This variable stores the data returned by the EventData() function. Subsequently, we insert this data into the AuditTable.

It's very importent to provide the fully qualified name here because this trigger operates at the server level. Merely specifying the table name won't suffice; we must include the database name, schema name, and the actual table name.

Additionally, we need to specify the columns of that table for which we want to insert values. When retrieving values from the XML, we utilize the value function and specify the XPath. Upon examining the XML data, we find that all event data is present within the EVENT_INSTANCE root element.

So here, we're specifying to go to the EVENT_INSTANCE element, and within that, we're extracting the database name. This retrieves the name of the database. Regarding the numerical number 1, you can consider it as equivalent to 'SELECT TOP 1' in Transact-SQL.

First, we retrieve the database name and convert it to a VARCHAR data type with a length of 300. Then, we insert it into the AuditTable.

Similarly, we retrieve the object name (table name), EventType, login name, Transact-SQL Command, and the DateTime when that change occurred.

create table tbldummy(Id int)

ALTER TABLE tbldummy
ADD Name varchar(255);

drop table tbldummy

Now, let's examine the contents of our AuditTable. We execute the query 'SELECT * FROM AuditTable' and observe that we have three entries. The first entry corresponds to the 'create table' event.

We can see the name of the database, the table name, the event type, the associated login name, the exact Transact-SQL command, and the audit DateTime.

Where to see the server scoped trigger?

A server-scoped trigger is created at the server level. You can locate it under the server objects folder. Upon expanding this folder, you'll find the triggers folder, and within that, you can locate our server-scoped trigger.