September 14, 2010 at 4:04 am
Hello all,
I have a DDL trigger set up which simply logs the DDL rename events to an auditing table. It works fine. But if an error occurs in the insert statement (for example, if someone makes a change to DBA.dbo.tDdlEvent, etc) then the entire event that caused the trigger fails.
What I'd like to do is add error handling to the DDL trigger so that if an error occurs during the insert, then the actual DDL event that fired the trigger still completes, but the insert is just skipped and a warning logged. I thought that the code below would do the trick, but it doesn't - if the insert fails, then the rename event also fails and is rolled back.
Does anyone know what I am missing?
Thanks in advance.
ALTER TRIGGER [trDdlEventServer]
ON ALL SERVER
FOR RENAME
AS
INSERTDBA.dbo.tDdlEvent (EventData)
SELECTEVENTDATA()
IF @@ERROR <> 0
EXEC master.dbo.xp_logevent 50001, 'An error occurred in the DDL trigger trDdlEventServer', WARNING
September 14, 2010 at 5:04 am
a try... catch block should do the trick to ensure the trigger completes
September 14, 2010 at 12:47 pm
Thanks for the suggestion. I tried using a TRY..CATCH block it but it did not change the behaviour: the event that caused the trigger to fire is also rolled back. The code I tried is shown below.
Does anyone else know how to resolve this?
ALTER TRIGGER [trDdlEventServer]
ON ALL SERVER
FOR RENAME
AS
BEGIN TRY
INSERT DBA.dbo.tDdlEvent (EventData)
SELECT EVENTDATA()
END TRY
BEGIN CATCH
EXEC master.dbo.xp_logevent 50001, 'An error occurred in the DDL trigger trDdlEventServer', WARNING
END CATCH
September 14, 2010 at 1:40 pm
it seems to me that the only error that could be raised that would crash the trigger would be the renaming of the table the trigger uses, right?
why not add code to skip the writing if it's that table?
Besides, if that specific table gets renamed, or the DBA database gets dropped the trigger will fail from that point on anyway.
ALTER TRIGGER [trDdlEventServer]
ON ALL SERVER
FOR RENAME
AS
declare @eventData XML,
@oname nvarchar(100)
SET @eventData = eventdata()
SELECT @oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')
IF oname <> 'tDdlEvent'
BEGIN
INSERT DBA.dbo.tDdlEvent (EventData)
SELECT EVENTDATA()
END
Lowell
September 14, 2010 at 2:11 pm
Thanks for your input.
"it seems to me that the only error that could be raised that would crash the trigger would be the renaming of the table the trigger uses, right?"
That is not correct. One example would be that if someone changes the datatype of DBA.dbo.tDdlEvent.EventData from XML to INT, for example. Another example woul be if someone changes the name of the DBA database. Or drops the DBA database. Or changes the schema of the table tDdlEvent from DBO to AUDIT. If any of those happen, then the trigger will fail, and any RENAME event that caused the trigger to fire will also fail. I don't want to cause legitimate changes to be rejected simply because this trigger fails to insert an audit record.
September 14, 2010 at 2:25 pm
ok, any operation which changes the definition of the audit table should be rolled back anyway, should it not? changing THAT specific table is NOT a legitimate change, as it is tied to your audit processes(which would break if the change were allowed) so instead of just RENAME for ALL SERVER, you should also have DROP_TABLE, ALTER_TABLE in the trigger and raise an error if your object being changed is your audit table, i think.
i'm not sure which event is fired for moving a table to a different schema, but that event would be needed as well.
my version of an auditing trigger for all server writes to a table in master, and then end users don't have permission to alter that table anyway; I'm not so worried that some dba would alter that base table;
take a look at mine and Vincent's auditing triggers from this thread and see if they help:
http://www.sqlservercentral.com/Forums/Topic751783-566-3.aspx
EITCL (9/14/2010)
Thanks for your input."it seems to me that the only error that could be raised that would crash the trigger would be the renaming of the table the trigger uses, right?"
That is not correct. One example would be that if someone changes the datatype of DBA.dbo.tDdlEvent.EventData from XML to INT, for example. Another example woul be if someone changes the name of the DBA database. Or drops the DBA database. Or changes the schema of the table tDdlEvent from DBO to AUDIT. If any of those happen, then the trigger will fail, and any RENAME event that caused the trigger to fire will also fail. I don't want to cause legitimate changes to be rejected simply because this trigger fails to insert an audit record.
Lowell
September 14, 2010 at 2:56 pm
Thanks again for your reply and input, I appreciate it. Sorry if it comes across as harsh, but I have some contrary ideas to your suggestions. Ultimately, though, this is good to assist understanding of this issue.
"any operation which changes the definition of the audit table should be rolled back anyway".
There are two things wrong with this statement. First, why should changing the definition of the audit table be rolled back? Auditing is just to assist administration: it should not prevent the normal operation of the production environment, whatever that may be.
Second, it is not the operation of changing the definition of the audit table that will be rolled back, anyway. The change to the audit table will occur without issue. Explicitly, a statement such as "ALTER TABLE tDdlEvent DROP COLUMN EventData; ALTER TABLE tDdlEvent ADD COLUMN EventData INT;" would complete with no error or issue. The problem is that subsequent RENAME operations will then begin to fail (because the trigger will fail), and these operations could be at any arbitraty time (days, weeks, months) after the change to the audit table.
"my version of an auditing trigger for all server writes to a table in master, and then end users don't have permission to alter that table anyway; I'm not so worried that some dba would alter that base table"
I agree, if you set permissions from the audit table such that few people can change it, then the possibility of a change occurring that breaks the trigger is small. But it is still possible. I would prefer to write code that would not leave things to chance and not break. And the piece of code I gave at the beginning of this thread should have done that.
The real issue is that transactions and error handing are not occurring as I expect in this circumstance. What I am aiming for here is some help understanding that side of things.
May 28, 2019 at 4:17 pm
A very old thread...but it still comes up when searching for this problem.
As the first line of the trigger, add
SET XACT_ABORT OFF;
Then you can trap errors in the trigger with TRY/EXCEPT as you see fit, and the original DDL command will succeed even if there is an error in the trigger.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply