DDL Trigger Example in SQL Server

By: Daniel Farina

Overview

In this chapter of the tutorial I will show you a use case for a database DDL trigger.

Sample Test Scenario

Suppose that you want to keep a log with 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.)

Database Trigger Using a Database Trigger to build an Audit table.

DDL Trigger Example in SQL Server

MENU

By: Daniel Farina

Overview

Sample Test Scenario

Additional Information

<< Previous

Next >>

Comments For This Article