Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from stackoverflow.com Feb 19 '12 at 17:20

This question came from our site for professional and enthusiast programmers.

2 Answers

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.

share|improve this answer
    
Not exactly, he wants to be notified in the case a ROLLBACK occurred. But it's a good advice nonetheless. –  Marian Feb 20 '12 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 Feb 20 '12 at 20:15
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.