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

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

In this article, we examine how to use a DDL trigger to verify table changes on an SQL server.

Is that what we want to do? We want to keep track of all table changes that occur in all our databases in this case from SQL Server.

How do I keep a history of changes in the database table?

For example, when someone creates a new table, we want to enter all the information. We want the name of the database where this table was created, the name of the table itself.

I have created a table in which the control information is stored.

Table SQL script

/****** object: Table [dbo]. Date of the scenario : 21.10.2020 12:21:58 ******/
ANSI_NULLS SET TO
GO

SET C QUOTED_IDENTIFIER TO
GO

CREATE TABLE [dbo].AuditTable](
[database name] [varchar](300) NULL,
[table name](300) NULL,
[event type] [varchar](300) NULL,
[connection name] [varchar](300) NULL,
[User name] [barbarian](300) NULL,
[SQL command] [barbarian](5000) NULL,
[Date change time] NULL
) ON [NOTE]
GO

And what is eventType, whether it creates a table, modifies a table, or launches a table, the logon name associated with the person who made the change, the exact SQL transaction command it executed. And when did these changes occur?

To get there? We will use the EVENTDATA() function. So what is this EVENTDATA() function? To understand this, let’s take an example.

When we create an array, we know that the DDL event is triggered and that this event is created.

The EVENTDATA() function now returns the data associated with this DDL event in XML format. And this is what the data will look like.

CREATE_TABLE
2020-10-21T11:47:48.820 56
ADEQUATE-ASHOKSQLEXPRESS01
adk
dbo
DemoDB
dbo
tbltest
TABLE

Tablet tbltest(Id int) making

Well, I pulled the trigger. The name of the trigger is tr_Geteventdataproproperties, and it is the trigger considered by the server that creates a table in response, modifies the table, and discards events from the table.

TRIGGER CREATE TRIGGER tr_captured data properties
ON ALL SERVERS
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
select EVENT DATA()
END

What if you look at what the trigger does?

simply selects what the EVENT DATA() function returns. So let’s pull the trigger.

So, if we now create a table, this trigger is executed and returns the data associated with this create_table event in the sample format.

Read Also: Best Ps3 Games: Exclusive Collection of 2019

Let’s see it in action. Okay, so we made a chart. So, when I make this notification, we get EVENT DATA() in XML format.

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

And when I click on it, you’ll see we get event data.
2

CREATE_TABLE
2020-10-21T11:47:48.820 56
ADEQUATE-ASHOKSQLEXPRESS01
adk
dbo
DemoDB
dbo
tbltest
TABLE

Tablet tbltest(Id int) making

So first we have a table for creating EventType – this is EventType, PostTime – every time this change occurs, the server process ID, server name, login name, user name, database name, schema name, object name which is the name of our table, object type, and the exact SQL transaction command we executed.

We can now use this newly generated XML data, all the information we need, and store it in AuditTable.

We now need to write a trigger to read this XML information and then insert this XML data into our AuditTable.

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

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/Event type)[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.

tr_trackchangestoatable is the name of the trigger, the server is processed and the trigger responds to three events.

And look what we’re doing in the body of the trigger. We create an XML variable called @EventData.

And in this variable we store all the data that the EventData() function will return, then we insert the data into the AuditTable.

3

It is now very important that we give a fully qualified name here because this is a trigger server with scoping.

If you only enter the name of the table, it will not be found. You have to give it the full name, the name of the database, the name of the schematic, and the actual name of the table.

And then the columns of the table for which we want to insert the values, and be careful to extract the values from XML.

We use the value function and indicate XPath. So if we look at the XML data, all event data are in the basic element EVENT_INSTANCE.

4

So this is an example of an event, and it has a database name in it. Get this and the name from the database. Then he’ll come back. And if you’re wondering what the number 1 is, you can consider it the first choice for SQL transactions.

5

So first we make the name of the database, and we want it to be converted into 300 for these barbarians. It’s some kind of data. And then we put it on the test track.

The same way we get the name of the object. In our case, that’s the name of the table. Next, the EventType, login name, SQL command, and DateTime communicate when this change occurs.

So let’s try to make a table, adjust it, and finally drop it.

create an array tbldummy (Id int)

OLD TABLE tbldummy
ADD Name varchar(255) ;

empty table

So we now have an audit on the table.  Let’s see what we’ve got. So select * in the AuditTable and enter a message that we have 3 entries and that the first entry is the creation table.

So we have the name of the database, the name of the table, the type of event, the corresponding login name, and the exact SQL transaction, date, and time verification.
<! -

create table tbldummy(Id int ) ALTER table tbldummy ADD name varchar ( 255 ) ; drop table tbldummy

width=676 height=295 />

Where can I see the server trigger?

The server trigger is actually created at the server level. Okay, we’ve got the Objects folder from the server. If we implement it, we have a trigger map, and if we implement it, we can find the trigger in the area of our server.
5

Related Tags:

database audit checklist,fn_get_audit_file,sql server database audit specification,sql server login audit script,database auditing tools,sql server audit performance impact,sql server change tracking performance,change_tracking_is_column_in_mask,sql server change tracking vs cdc,mysql change tracking,sql server change data capture,how to find database changes in sql server,sql server audit trigger xml,sql update trigger log changes,how to create audit table in mysql,trigger in ssms,log table using trigger,create log table in sql server,sql server audit filter,sql server login auditing,sql server track ddl changes,azure sql change tracking,select rows where column value has changed,sql check if value changed,sql server track who changed what,how to build a trigger sql,change tracking in azure sql database,mysql audit trail of updates and deletes,generatetriggers sql,audit fields in sql server,sql audit trail script,sql server audit trigger for insert, update, delete,database audit table best practice,how to find table changes in sql server,sql server track changes to a table,sql server dml triggers to track all database changes,sql server query audit log,sql server auditing best practices,create database audit specification for all tables

About the Author: prateek

You May Also Like