DDL Triggers and SET options

- March 07, 2011

I have just had an unforgettable experience with DDL triggers and, as far as I see on Google search, I'm not alone.

DDL triggers, as you know, fire stored procedure in a response to server or database DDL events, like metadata or security changes. We use them to track changes on publishing server (those changes that replication cannot take care of) and apply them on subscriber. So, DDL triggers procedure writes statement into Statement table and other process later is executing them on subscriber.

Here is a code of DDL trigger and statements logging table below.

################################################################################

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE dbo.DDLOutput(

ID int IDENTITY(1,1) NOT NULL,

DateAdded datetime NULL,

EventType nvarchar(255) NULL,

DatabaseName sysname NULL,

SchemaName sysname NULL,

ObjectName sysname NULL,

ObjectType nvarchar(255) NULL,

Statement nvarchar(max) NULL,

LoginName sysname NULL,

CONSTRAINT PK_DDLOutput PRIMARY KEY NONCLUSTERED ( ID )

)

################################################################################

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TRIGGER [TRG_DDLServer]

ON ALL SERVER

FOR

DDL_EVENTS

BEGIN TRY

DECLARE @PostTime datetime

DECLARE @EventType nvarchar(max)

DECLARE @DatabaseName nvarchar(max)

DECLARE @SchemaName nvarchar(max)

DECLARE @ObjectName nvarchar(max)

DECLARE @ObjectType nvarchar(max)

DECLARE @CommandText nvarchar(max)

DECLARE @LoginName nvarchar(max)

DECLARE @error nvarchar(2048)

SELECT

@PostTime = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime'),

@EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)'),

@DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(max)'),

@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)'),

@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)'),

@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)'),

@CommandText = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),

@LoginName = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)')

INSERT INTO DDLOutput(DateAdded,EventType,DatabaseName,SchemaName,ObjectName,ObjectType,Statement,LoginName)

VALUES(@PostTime,@EventType,@DatabaseName,@SchemaName,@ObjectName,@ObjectType,@CommandText,@LoginName)

END TRY

BEGIN CATCH

SET @error = 'Failed execute DDL trigger:' + ERROR_MESSAGE()

ROLLBACK;

RAISERROR (@error,18,1) WITH LOG

END CATCH

################################################################################

After table and DDL trigger creation, nice thing that is worth mentioning, is that you cannot drop or rename this table, you will get “Invalid object” error message. Only after disabling the trigger you can drop or rename it.

After Creating trigger and table try to execute the following statement

CREATE TABLE test1 ( i int)

Now, you can see in the logging table all the details of your statements: script itself, loginname, database where the statement was executed and so on.

Now try to execute the following:

SET ANSI_PADDING OFF

CREATE TABLE test2 ( i int)

You will get error message:

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted

Failed execute DDL trigger:SELECT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

The reason for this failure ( I must admit that it took me hours to get it work properly) is because trigger is using XQuery and in order for use XQuery those settings must be on: QUOTED_IDENTIFIER, , ANSI_WARNINGS, ANSI_PADDINGS, ANSI_NULLS, CONCAT_NULL_YELDS_NULL. NUMERIC_ROUNDABORT must be off.

I have tried setting ANSI_DEFAULTS ON, which should include first four settings but, for some weird reason, trigger still fails if I do not explicitly add each setting separately.

Your trigger should look like that:

################################################################################

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TRIGGER [TRG_DDLServer]

ON ALL SERVER

FOR

DDL_EVENTS

BEGIN TRY

DECLARE @PostTime datetime

DECLARE @EventType nvarchar(max)

DECLARE @DatabaseName nvarchar(max)

DECLARE @SchemaName nvarchar(max)

DECLARE @ObjectName nvarchar(max)

DECLARE @ObjectType nvarchar(max)

DECLARE @CommandText nvarchar(max)

DECLARE @LoginName nvarchar(max)

DECLARE @error nvarchar(2048)

SET ANSI_PADDING ON

SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

SET QUOTED_IDENTIFIER ON

SET CONCAT_NULL_YIELDS_NULL ON

SET NUMERIC_ROUNDABORT OFF