Blog

Aaron Bertrand has written a great introduction to server/database DDL triggers in his article SQL Server DDL Triggers to Track All Database Changes.

I wanted to implement similar DDL triggers on more or less all servers. Some of the databases on those servers were from a 3:rd party, so changes in those databases wasn’t possible. Therefore I wanted to make sure these DDL triggers were “zero footprint”. That is, not causing existing database code to fail. I simply added TRY and an empty CATCH to the trigger, believing I’d be safe. Unfortunately, an error like this…

…happened to a SQL Agent job running during the evening.

Investigating, I found that the reason was that the DDL trigger make use of XQuery to get information on the event via the EVENTDATA() function, and that this and other XML methods are sensitive to various ANSI settings. In the job that failed it was a procedure that issued a SET ANSI_WARNINGS OFF statement (for some reason or another).

Lets see what happens when a DDL trigger gets excecuted under the “wrong” ANSI settings. We’ll create a simple DDL trigger that simply reads from EVENTDATA() and nothing else, and also a stored procedure that creates a table, but first it issues a SET ANSI_WARNINGS OFF command. Perhaps not the most realistic scenario, but it’s for demonstration purposes only. Executing that stored procedure will then raise an DDL event that is captured by the DDL trigger:

--CREATE THE DDL TRIGGER
CREATE TRIGGER DDL1 ON ALL SERVER FOR DDL_SERVER_LEVEL_EVENTS, DDL_DATABASE_LEVEL_EVENTS

AS

	DECLARE @EventData XML 
	SET @EventData = EVENTDATA()

	DECLARE @EventType VARCHAR(MAX)
	SELECT @EventType = @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

GO


--CREATE SP TO TEST
CREATE PROC SP1 AS

	--This SET statement is there only for demonstration purposes.
	SET ANSI_WARNINGS OFF
	CREATE TABLE T1 (C1 INT)
	DROP TABLE T1

GO


--EXECUTE SP
EXEC SP1
GO

This generates an error:

Note that the stored procedure would have worked without the DDL trigger. If we disable or drop the trigger, the stored procedure will work. Try it if you like.

Of course to avoid the error, the better action in my example would be to not “SET ANSI_WARNINGS OFF” in the first place. But sometimes the code is out of your control, and it is possible that the “wrong” setting are used.

Let’s try the trigger with error handling:

ALTER TRIGGER DDL1 ON ALL SERVER FOR DDL_SERVER_LEVEL_EVENTS, DDL_DATABASE_LEVEL_EVENTS

AS
	
	DECLARE @EventData XML 
	SET @EventData = EVENTDATA()

	BEGIN TRY
		DECLARE @EventType VARCHAR(MAX)
		SELECT @EventType = @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
	END TRY

	BEGIN CATCH

	END CATCH
GO


EXEC SP1
GO

This is the error I got in the first place, from the SQL Agent job.

Note that there is no reference to the DDL trigger in the error message, so that the DDL trigger is the cause to the error is far from obvious. I realized my DDL trigger had something to do with the error since I created it earlier the same day, but if this error would have happened a couple of months later, it would not be so easy to see that the DDL trigger is causing the error.

Using THROW will get you a much better error message in this case: “…SELECT failed because the following SET options have incorrect settings: ANSI_WARNINGS…“. This is the same error message as without error handling.

To issue a rollback in the CATCH part won’t help either, which will raise the error:

To summarize:

As I understand it, the reason that no workaround can be found to the above errors is that the transaction becomes “uncommitable”. And no matter what we do, an error will occur. This uncommitable state that we are in can be seen by querying the function XACT_STATE(), that if called in this trigger always reports -1. More information here.

From MSDN on why we enter a uncommitable state:

A transaction enters an uncommittable state inside a TRY block when an error occurs that would otherwise have ended the transaction. For example, most errors from a data definition language (DDL) statement (such as CREATE TABLE)…

But calling the EVENTDATA() function in itself should not cause the transaction to become uncommitable. Remember, without the trigger no error occurs at all. Somehow this is “inherited” from the “CREATE TABLE” statement. It seems SQL Server is keeping track of the fact that an DDL statement has been issued. Interestingly, if the table that is created in the stored procedure is an temp table instead, there is no error.

The only way I have found to avoid this problem is to make sure the correct ANSI settings are used in the DDL trigger:

ALTER TRIGGER DDL1 ON ALL SERVER FOR DDL_SERVER_LEVEL_EVENTS, DDL_DATABASE_LEVEL_EVENTS

AS
	--TO AVOID ERRORS...
	SET ANSI_WARNINGS ON
	
	DECLARE @EventData XML 
	SET @EventData = EVENTDATA()

		DECLARE @EventType VARCHAR(MAX)
		SELECT @EventType = @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

GO

Now the stored procedure will work without errors.

So, what other ANSI settings are there that causes this behavior? From MSDN again:

When you are creating and manipulating indexes on computed columns or indexed views, the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option NUMERIC_ROUNDABORT must be set to OFF.

(There is no mention of “XML data type methods” as in the error message 1934 above, but the rule seems to apply for XML as well.)

Ok, so the “zero footprint” DDL trigger needs to issue the following SET statements:

ALTER TRIGGER DDL1 ON ALL SERVER FOR DDL_SERVER_LEVEL_EVENTS, DDL_DATABASE_LEVEL_EVENTS

AS
	--ALL SETTINGS NEEDED FOR XQUERY IN DDL TRIGGERS
	SET ARITHABORT, CONCAT_NULL_YIELDS_NULL, ANSI_PADDING, ANSI_WARNINGS ON
	SET NUMERIC_ROUNDABORT OFF
	
	DECLARE @EventData XML 
	SET @EventData = EVENTDATA()

		DECLARE @EventType VARCHAR(MAX)
		SELECT @EventType = @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

GO

There is no need to set the ANSI setting “back” to what they were, because:

If a SET statement is run in a stored procedure or trigger, the value of the SET option is restored after control is returned from the stored procedure or trigger.

As you can see, I didn’t include the settings for QUOTED_IDENTIFIER and ANSI_NULLS in the trigger. That is because those are set at trigger creation time, not in run time:

Stored procedures specifying SET ANSI_NULLS or SET QUOTED_IDENTIFIER use the setting specified at stored procedure creation time. If used inside a stored procedure, any SET setting is ignored.

(Triggers aren’t mentioned, but the same applies.)

So you need to make sure ANSI_NULLS or SET QUOTED_IDENTIFIER are correct (ON) before you issue the CREATE/ALTER TRIGGER statement:

--SETTINGS WHEN CREATING THE TRIGGER
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO


ALTER TRIGGER DDL1 ON ALL SERVER FOR DDL_SERVER_LEVEL_EVENTS, DDL_DATABASE_LEVEL_EVENTS

AS
	--ALL SETTINGS NEEDED FOR XQUERY IN DDL TRIGGERS
	SET ARITHABORT, CONCAT_NULL_YIELDS_NULL, ANSI_PADDING, ANSI_WARNINGS ON
	SET NUMERIC_ROUNDABORT OFF
	
	DECLARE @EventData XML 
	SET @EventData = EVENTDATA()

		DECLARE @EventType VARCHAR(MAX)
		SELECT @EventType = @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

GO

ANSI_NULLS, QUOTED_IDENTIFIER ON are the the default settings.

You may have noticed Management Studio will include these settings for you when you script an object, but watch out for the bug that turns ANSI_NULLS and QUOTED_IDENTIFIER OFF as the last statements when you script a DDL trigger:

There is a connect item regarding this bug here.

Status is closed but I don’t know if there is a fix or not. Problem is, if you encounter this bug, your connection will have those ANSI settings turned off after executing the scripted trigger. If you for instance create or alter a stored procedure right after the trigger script (with the same connection), that stored procedure will have ANSI_NULLS and QUOTED_IDENTIFIER OFF.