Overview
In this chapter of the tutorial, I will show you a use case for a database DDL trigger.
Explanation
Sample Test Scenario
Suppose that you want to keep a log of the changes made to table definitions in a given database. For example, this could be useful in development environments when multiple developers are working on the same database or even in a production environment to keep track of deployments.
The basic idea is to create a Database DDL trigger in our test database that fires when someone executes an ALTER TABLE statement scoped to our test database.
First, here is the code for the audit table on which we will insert the audit records.
CREATE TABLE AuditTable
(
AuditRecordID INT IDENTITY(1, 1) ,
EventType VARCHAR(128) ,
PostTime VARCHAR(128) ,
SPID INT ,
UserName VARCHAR(128) ,
DatabaseName VARCHAR(128) ,
SchemaName VARCHAR(128) ,
ObjectName VARCHAR(128) ,
ObjectType VARCHAR(128) ,
Parameters VARCHAR(2000) ,
AlterTableActionList VARCHAR(2000) ,
TSQLCommand VARCHAR(2000)
);
Finally, here is the code for the database trigger.
CREATE OR ALTER TRIGGER TR_Schema_Change ON DATABASE
FOR DDL_TABLE_VIEW_EVENTS
AS
DECLARE @EventData XML;
SET @EventData = EVENTDATA();
INSERT INTO dbo.AuditTable ( EventType ,
PostTime ,
SPID ,
UserName ,
DatabaseName ,
SchemaName ,
ObjectName ,
ObjectType ,
Parameters ,
AlterTableActionList ,
TSQLCommand )
VALUES (@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(128)') ,
@EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'VARCHAR(128)') ,
@EventData.value('(/EVENT_INSTANCE/SPID)[1]', 'VARCHAR(128)') ,
@EventData.value('(/EVENT_INSTANCE/UserName)[1]', 'VARCHAR(128)') ,
@EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(128)') ,
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'VARCHAR(128)') ,
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(128)') ,
@EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'VARCHAR(128)') ,
@EventData.value('(/EVENT_INSTANCE/Parameters)[1]', 'VARCHAR(128)') ,
@EventData.value('(/EVENT_INSTANCE/AlterTableActionList)[1]', 'VARCHAR(128)') ,
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
);
GO
In order to test this trigger, I will create a table, add a column, and then drop the table.
CREATE TABLE t1(a INT) ALTER TABLE t1 ADD b INT DROP TABLE t1 SELECT * FROM dbo.AuditTable
In the next screen capture, we can see the results obtained when running the previous script. (Note: the image below was modified to show all columns.)
Additional Information
- Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005
- Grant Execute Permissions to SQL Server Stored Procedures using DDL Triggers
- SQL Server DDL Triggers to Track All Database Changes
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning. He studied at Universidad de Buenos Aires. Daniel started working as a programmer at a young age. Over the years he specialized in databases, particularly SQL Server and Oracle. Now with 30 years of age, his work experience includes working with various technologies like VB, C, .NET, web development, Windows and Linux systems. He likes to read about science, psychology, philosophy and many other things. In his spare time, he trains powerlifting aiming to compete.
- MSSQLTips Awards: Author of the Year – 2018 | Champion (100+ tips) – 2018 | Author Contender – 2015-2017, 2019
