5

I'd like DDL changes in SQL Server to trigger an external process (outside of SQL). The problem is, is there a way for me to be notified via my trigger that a rollback occurred?

For instance, with the following:

BEGIN TRAN

CREATE TABLE [dbo].[TestRollbackTable](
    col1 varchar(100) NOT NULL)

ROLLBACK

A DDL trigger on CREATE_TABLE would be called, but nothing seems to be called with the ROLLBACK. And since my trigger is calling an external process, it is not automatically subjected to the rollback. How can I catch the fact that the ROLLBACK negated that action?

Improve this question

2 Answers 2

Reset to default
3

Don't use DDL triggers. Use Event Notifications instead. Event Notifications convey the very same data as DDL triggers and occur on the very same events, but they are asynchronous and loosely coupled. You get notified only after the original DDL committed. If the DDL rolls back, you don't get any notification. It seems to be exactly what you need.

Improve this answer
2
  • Not exactly, he wants to be notified in the case a ROLLBACK occurred. But it's a good advice nonetheless.
    – Marian
    Commented Feb 20, 2012 at 15:28
  • 1
    @Marian he wants notification of a ROLLBACK because he always gets a notifcation regardless of success. This would eliminate that need.
    – JNK
    Commented Feb 20, 2012 at 20:15
1

You cannot be notified of rollbacks.

Think about what was if you could: You could for example create a new table during a running rollback. Would that new table be present after the rollback was complete or would it also be rolled back? What if your notification routine caused another rollback? Now you have nested rollbacks.

I think that these hard questions are the reason that the SQL Server people chose not to provide that capability.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.