Audit table changes in sql server

sql server 2008 ddl trigger audit
sql server table changes tracking

In this video we will discuss, how to audit table changes in SQL Server using a DDL trigger.

Table to store the audit data
Create table TableChanges
(
DatabaseName nvarchar(250),
TableName nvarchar(250),
EventType nvarchar(250),
LoginName nvarchar(250),
SQLCommand nvarchar(2500),
AuditDateTime datetime
)
Go

The following trigger audits all table changes in all databases on a SQL Server.
CREATE TRIGGER tr_AuditTableChanges
ON ALL SERVER
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
DECLARE @EventData XML
SELECT @EventData = EVENTDATA()

INSERT INTO SampleDB.dbo.TableChanges
(DatabaseName, TableName, EventType, LoginName, SQLCommand, AuditDateTime)
VALUES
(
@EventData.value(‘(/EVENT_INSTANCE/DatabaseName)[1]’, ‘varchar(250)’),
@EventData.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘varchar(250)’),
@EventData.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘nvarchar(250)’),
@EventData.value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘varchar(250)’),
@EventData.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘nvarchar(2500)’),
GetDate()
)
END

In the above example we are using EventData() function which returns event data in XML format.

Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/09/audit-table-changes-in-sql-server.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/09/audit-table-changes-in-sql-server_12.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

Full SQL Server Course

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists