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.
And when I click on it, you’ll see we get event data.
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.
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.
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.
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.
