After a considerable amount of testing, I finally discovered the reason behind this error. The client connection explicitly set ANSI_WARNINGS and CONCAT_NULL_YIELDS_NULL OFF. XML data operations, such as @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), require both to be ON. I had attempted to override these within the trigger, but I may have placed them wrong. The final code below works, even with the explicit SET options in the connections from Great Plains:
CREATE TRIGGER [ddl_log]
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS, DDL_SERVER_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;
DECLARE @data XML
SET @data = EVENTDATA()
EXECUTE AS LOGIN='<dummy login>'
INSERT admin.dbo.ddl_audit (PostTime, DB_User, [Event], [TSQL], Host, DatabaseName)
VALUES (
GETDATE(),
CONVERT(NVARCHAR(100), ORIGINAL_LOGIN()),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
CONVERT(NVARCHAR(100), HOST_NAME()),
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)')
) ;
REVERT
END
As an alternative, I also could have simply inserted EVENTDATA() as an XML LOB into a table, rather than parsing it out into columns. Because I would not be manipulating the XML, the SET options do not matter. Then I would just build an XML index for querying performance, and construct a view to use for audit log reporting that parses the XML in the view definition, in the same manner as I am doing above in my INSERT statement.
Thanks to Max for pointing me in a different research direction, and @AaronBertrand on #sqlhelp who helped me with correct SET options within the body of the trigger.
It is possible to disable a trigger. (The user could have disabled it) Also, it is possible that there are other triggers that return an error and rolls back the transaction, wich means the after trigger is not called.
Best Answer
Sadly:
Disabling a trigger is not altering the trigger, just like turning off a light is not altering the lightbulb. However it is funny that it is not captured by
ALTER_TRIGGERbut something still manages to updatemodify_datewhen it happens. ¯\-(ツ)-/¯Your DDL trigger catches the
ALTER TABLEvariation because that is explicitly altering the table - it says so in the command. While you could argue that disabling the trigger is altering the table regardless of the syntax you use (and I would agree with you, especially since the net result is the same), the difference is that this is currently a gap in what DDL triggers capture, and there is noDISABLE_TRIGGERevent (you can vote for them to fix it in this feedback item on UserVoice, but that has been sitting there completely ignored for over a decade). Here is a related, understandably unanswered question on Stack Overflow.Similarly, a long time ago I remember the
ALTER_events didn't captureRENAME, and this was added as an event later.You'll have to catch
DISABLE TRIGGERin other ways, like audit or Extended Events (I talked about this here):In which case, you may as well capture the
ALTER TABLEvariation the same way so they're both logged in the same place.A really dumb idea would be to poll
sys.triggersevery n seconds/minutes/hours and log whenever the state ofis_disabledchanges. This won't let you see who did it but at least you'll know that it happened and, along with other auditing, you may be able to narrow it down.