How to Log record changes in SQL server in an audit table

In this post, we’ll discuss how to audit table changes in SQL server using a DDL trigger.

So here’s what we want to do? We want to keep track of all the table changes that are happening across all our databases in a given SQL server instance.

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

For example, when somebody creates a new table, we want to capture all information. We want to capture the name of the database in which that table is created, the table name itself.

For that purpose, I have created an auditable table which stores 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

And what’s the eventType, whether it creates table, alter table, or drop table, the login name associated with the person who made that change, the exact transact SQL command that they have executed. And what is the DateTime when that change happened?

In order to achieve this? We’re going to make use of EVENTDATA() function. So what does this EVENTDATA() function? Now, to understand this, let’s take an example.

When we create a table, we know that a DDL event is triggered and that event is created_table.

Now, EVENTDATA() function is going to return us the data associated with that DDL event, and it’s going to return that in XML format. And this is how the data is going to look like.

<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>

So here I have got a trigger. The name of the trigger is tr_Geteventdataproperties and this is a server scoped Trigger and this will file in response to create table, alter table, and drop table events.

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

And if look at what the trigger is doing?

it’s simply selecting whatever this EVENTDATA() function returns. So let’s go ahead and create this trigger.

So now if we create a table, this trigger will fire and it’s going to return the data associated with this create_table event in an example format.

Let’s look at that in action. So here we have create table statement. So when I execute that notice, we get the EVENTDATA() in an 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>

So first we have the EventType- create table is the EventType, PostTime- whenever this change happened, the server process ID, the server name, login name, username, database name, schema name, object name that is our table name, object type and the exact transacts SQL command that we have executed.

Now we can use this XML data, reterived, whatever pieces of information that we want and store them in AuditTable.

Now we have to write the trigger to read that XML information and then insert that XML data into our AuditTable table.

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

So tr_trackchangestoatable is the name of the trigger, its server scoped and the trigger is going to respond to three events

And look at what we are doing within the body of the trigger. We are creating a variable of type XML and the name of the variable is @EventData.

And in this variable we are storing whatever data EventData() function is going to return and then we are inserting data into table AuditTable.

Now it’s very important that we give the fully qualified name here because this is a server scoped Trigger.

if you just specify the name of the table, it is not going to find that table. you have to give it the fully qualified name, the database name, schema name, and the actual table name.

And then the columns for that table for which we want to insert the values and notice to retrieve the values from the XML.

We are using the value function and we are specifying the XPath . So if we look at XML data, all event data is present in EVENT_INSTANCE root element.

So here we are saying in a go-to event instance and within that, we have a database name. Retrieve that and the name of the database. So it’s going to retrieve that. And you feel wondering what is this numerical number 1, you can think of this one as select top one in transacts SQL.

So first is going to return the database name and we want that to be converted to that varchar of 300. That’s the data type. And then we are going to, insert that into the AuditTable.

Similarly, we are retrieving the object name. That is the table name in our case. And then then EventType, login name transacts, SQL Command, and the DateTime when that change happened.

So let’s try to create a table, alter this table and And then finally, drop that table.

create table tbldummy(Id int)

ALTER TABLE tbldummy
ADD Name varchar(255);

drop table tbldummy

so now within our AuditTable table.  Let’s see if that’s what we have there. So select * from AuditTable and we execute that notice that we have 3 entries and the first entry is create table.

So we have the name of the database, the name of the table, the event type, the login name associated, and the exact transact SQL, the audit DateTime.

where to see the server scoped trigger?

Server scoped trigger is actually created on the server level. So we have the server objects folder. If we expand that, we have got triggers folder and when we expand that, we can find our server scoped trigger there.

The post How to Log record changes in SQL server in an audit table appeared first on Software Development | Programming Tutorials.