T-SQL TRY-CATCH not working in DDL trigger
I have a DDL trigger to audit any changes to DDL events that occur on the
server.
The code in the trigger reads the eventdata and writes it to a table.
I want to wrap that operation in a TSQL try-catch so if it fails for any
reason then I would log the fault to the SQL log but let the operation go
through, but it doesn't seem to work. I am already using if exists to make
sure the table I need to write to still exists, but I want to trap any
unforseen errors and make the trigger as robust as possible.
DDL triggers seem to work differently than normal T-SQL and doesn't seem
to honour the TRY-CATCH block.
The following code works fine if it is in an SP but it doesn't work if it
is in a DDL trigger.
BEGIN TRY
-- Simulate an error
RAISERROR ('Just a test!', 14, 1);
END TRY
BEGIN CATCH
DECLARE @errorNumber int = ERROR_NUMBER()
DECLARE @errorMessage NVARCHAR(2048) = ERROR_MESSAGE() + '(' +
CAST(ERROR_NUMBER() AS NVARCHAR) + ')'
-- Log the error to the SQL server event log
EXEC master..xp_logevent 50005, @errorMessage, WARNING
END CATCH;
Any ideas?
No comments:
Post a Comment