This is the fourth post from my short 5 part series in which I demonstrate you 5 different ways to track database schema changes. Thanks to this, you always can easily answer questions about what has changed, who changed it and when. Today, I describe how to use DDL Trigger for this purpose.
Here you can find my previous posts from this series:
DDL Triggers
The fourth option is a DDL Trigger. These kinds of triggers are executed in response to Data Definition Language (DDL) events. They can be created in the database or server scope. Database-scoped DDL triggers are stored as objects in the database in which they are created. Server-scoped DDL triggers are stored in the master database. They can be created to be fired in response to a single particular event or the event from a predefined event group.
In our case, we are interested in capturing all events from the DDL_DATABASE_LEVEL_EVENTS group.
To get information about the event that fired our DDL trigger, we use the EVENTDATA()
function. It returns an XML value that contains details about the event that triggered the execution of the trigger.
Create DDL Trigger
To log objects’ changes we first have to create a table to which we will save captured events. Below code snippet shows the simplest possible table structure that can be used for this purpose.
CREATE TABLE [dbo].[DatabaseLogs]( [Id] [int] IDENTITY(1,1) NOT NULL, [DateTime] [datetime] NOT NULL CONSTRAINT DF_DatabaseLogs_DateTime DEFAULT (GETDATE()), [EventData] [xml] NULL, CONSTRAINT [PK_DatabaseLogs] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) GO CREATE NONCLUSTERED INDEX nix_DatabaseLogs ON [dbo].[DatabaseLogs] ([DateTime] ASC) INCLUDE ([Id]); GO
The trigger can be created using this query.
CREATE TRIGGER [tr_DatabaseLogs] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS BEGIN SET NOCOUNT ON; IF OBJECT_ID('dbo.DatabaseLogs') IS NOT NULL BEGIN BEGIN TRY DECLARE @Eventdata XML; SET @Eventdata = EVENTDATA(); INSERT dbo.DatabaseLogs ( [DateTime] , [EventData] ) VALUES ( GETUTCDATE() , @Eventdata ); END TRY BEGIN CATCH SET @Eventdata= NULL; END CATCH END END GO
Pay attention to the additional checks that are implemented in case if the table is dropped or if the insert statement fails. It is critical to keep such DDL trigger transparent to the users and applications to not impact their work if something goes wrong.
Viewing logged events
Execute this simple SELECT statement to view the log of captured object modifications.
SELECT * FROM dbo.DatabaseLogs ORDER BY [DateTime];
Here, you have an example of one of the inserted XMLs.
<EVENT_INSTANCE> <EventType>CREATE_PROCEDURE</EventType> <PostTime>2018-11-17T17:52:51.700</PostTime> <SPID>54</SPID> <ServerName>MAREK-PC\SS2017</ServerName> <LoginName>Marek-PC\Marek</LoginName> <UserName>dbo</UserName> <DatabaseName>TrackMyChanges</DatabaseName> <SchemaName>dbo</SchemaName> <ObjectName>usp_NewProc</ObjectName> <ObjectType>PROCEDURE</ObjectType> <TSQLCommand> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> <CommandText>CREATE PROCEDURE dbo.usp_NewProc AS BEGIN SELECT 'Version 1'; END </CommandText> </TSQLCommand> </EVENT_INSTANCE>
Of course, it is possible to extract this detailed information in the trigger itself and insert them into additional columns in the logging table.
Extended DDL Trigger
As a first step, we have to extend our logging table.
CREATE TABLE [dbo].[DatabaseLogs]( [Id] [int] IDENTITY(1,1) NOT NULL, [DateTime] [datetime] NOT NULL CONSTRAINT DF_DatabaseLogs_DateTime DEFAULT (GETDATE()), [ServerName] [nvarchar](128) NULL, [ServiceName] [nvarchar](128) NULL, [SPID] [int] NULL, [SourceHostName] [nvarchar](128) NULL, [LoginName] [nvarchar](128) NULL, [UserName] [nvarchar](128) NULL, [SchemaName] [nvarchar](128) NULL, [ObjectName] [nvarchar](128) NULL, [TargetObjectName] [nvarchar](128) NULL, [EventType] [nvarchar](128) NULL, [ObjectType] [nvarchar](128) NULL, [TargetObjectType] [nvarchar](128) NULL, [EventData] [xml] NULL, CONSTRAINT [PK_DatabaseLogs] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) GO CREATE NONCLUSTERED INDEX nix_DatabaseLogs ON [dbo].[DatabaseLogs] ([DateTime] ASC) INCLUDE ([Id]); GO
Such table structure should be sufficient to meet most needs. As the next step, we must implement XML parsing logic in our DDL Trigger. To do this, we use SQL Server XQuery methods.
CREATE TRIGGER [tr_DatabaseLogs] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS BEGIN SET NOCOUNT ON; IF OBJECT_ID('dbo.DatabaseLogs') IS NOT NULL BEGIN BEGIN TRY DECLARE @Eventdata XML; SET @Eventdata = EVENTDATA(); INSERT dbo.DatabaseLogs ( [DateTime] , [ServerName] , [ServiceName] , [SPID] , [SourceHostName] , [LoginName] , [UserName] , [SchemaName] , [ObjectName] , [TargetObjectName] , [EventType] , [ObjectType] , [TargetObjectType] , [EventData] ) VALUES ( GETUTCDATE() , @@SERVERNAME , @@SERVICENAME , @Eventdata.value('(/EVENT_INSTANCE/SPID)[1]', 'int') , HOST_NAME() , @Eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)') , @Eventdata.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(128)') , @Eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(128)') , @Eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)') , @Eventdata.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'nvarchar(128)') , @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)') , @Eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(128)') , @Eventdata.value('(/EVENT_INSTANCE/TargetObjectType)[1]', 'nvarchar(128)') , @Eventdata ); END TRY BEGIN CATCH SET @Eventdata= NULL; END CATCH END END GO
Thanks to this solution we can easily filter logged events without the need to write complicated ad-hoc queries using XQuery.
What was captured by DDL Trigger?
DDL Triggers capture very similar pieces of information to the Extended Events Sessions. We know what object was changed, when it was done, and by whom. We also have access to the SQL query that was executed.
Data retention
In this case, there is no default mechanism that clean up old data. If you need one, you have to implement it by yourself. Otherwise, the logging table will contain all events since DDL Trigger has been created. That, of course, has some advantages and disadvantages.
DDL Triggers summary
- Contains a lot of details
- Info WHAT exactly was changed
- Access to the executed SQL statement
- Info WHEN object was changed
- Info by WHO object was changed
- Data logged to the user table
- Easy to set up
- Possibility to set additional filters
- Easy viewing
- No default data retention options
- No access to the old object definition
In the next part, I will demonstrate you how to use SQL Server Audit to capture database schema changes.
-Marek
Share it:
You cannot use MEMORY OPTIMIZED tables in database that has DDL trigger active.
Are all of the columns in the final version of the logging table really NULLable? That seems surprising.
Another disadvantages of DDL trigger are that it rolls back the operation whenever log table is not accessible (either lack of permissions if you do not impersonate the trigger, or unavailability of a table – especially when you track multiple databases with a single table in a dedicated DBA’s database) and it is synchronous. Personally, I would recommend DDL triggers as a weapon against performing unwanted changes, but not for logging.