DB Best blog

ROLLBACK TRIGGER is one of those Sybase Adaptive Server Enterprise commands which do not have direct equivalent in SQL Server. SQL Server Migration Assistant does not support automatic conversion of this command, and the reason is impossibility to suggest one simple solution that is local for the module and good for all cases. Below I will try to explain the challenges of this conversion and my own suggestions about emulating ROLLBACK TRIGGER in SQL Server 2005.

This command is typically used in triggers and it has the following format:

ROLLBACK TRIGGER [WITH <RAISERROR statement>]

It cancels all updates made by the trigger and the triggering statement. Optionally, it can execute a RAISERROR statement.

First thing I would like to tell about its emulation is about what NOT to do. It is not a good idea to emulate this behavior by simple ROLLBACK command inside a SQL Server trigger, because the differences between ROLLBACK and ROLLBACK TRIGGER are very significant.

ROLLBACK command issued inside a SQL Server trigger terminates the whole batch, not just the trigger and the triggering statement. The things get even worse if there is an active transaction. ROLLBACK will revert back all the updates that have been made since the transaction was started, the thing Sybase ROLLBACK TRIGGER never attempts to do. As the transaction is in fact terminated by ROLLBACK, you will find that the commands following the triggering statement become out of the transaction scope and therefore can no more be committed or rolled back as a single unit. In Sybase, ROLLBACK TRIGGER cancels only the changes invoked by the triggering statement, while an outer transaction remains active. If you commit the outer transaction, all updates will be saved EXCEPT the triggering statement and triggers invoked by it. That means Sybase knows how to perform some kind of local rollback.

To emulate this behavior in SQL Server we should execute BEGIN TRANSACTION <savepoint> before the triggering statement. Here <savepoint> is an arbitrary identifier of the position for the rollback. If some transaction is already active at that moment, execute SAVE TRANSACTION <savepoint> instead. In the trigger, replace ROLLBACK TRIGGER with the two following statements:

RAISERROR (<the text of original RAISERROR>, 16, 1) WITH SETERROR

RETURN

Here RETURN statement is necessary when the ROLLBACK TRIGGER is not the last executable statement in the trigger, as RAISERROR itself does not terminate the execution flow. SETERROR clause sets the @@ERROR variable to 50000 so that you can test that value after the triggering statement like this:

IF (@@TRANCOUNT = 0)

BEGIN TRANSACTION <savepoint>

ELSE

SAVE TRANSACTION <savepoint>

<Triggering statement>

IF (@@ERROR = 50000) ROLLBACK TRANSACTION <savepoint>

Note that it is also possible to catch the exception so that the whole process runs silently (i.e. without error messages):

BEGIN TRY

<Triggering statement>

END TRY

BEGIN CATCH

IF (@@ERROR = 50000) ROLLBACK TRANSACTION

END CATCH

Unfortunately, this last variant does not allow rolling back to a savepoint: only the whole transaction can be rolled back in this case.

These commands work exactly like ROLLBACK TRIGGER in Sybase, but note that we must include a few additional statements any time when we want the source statement to be executed. As a workaround, you can wrap up these actions into a stored procedure.

And finally, there exists a more elegant solution for ROLLBACK TRIGGER problem. You can rewrite the trigger so that its type is changed from AFTER to INSTEAD OF. In that case, you will actually execute the triggering statement inside the trigger.

For example, if the triggering statement is INSERT, the INSTEAD OF trigger should look like this:

CREATE TRIGGER <name> on <table> INSTEAD OF INSERT AS

BEGIN

SAVE TRANSACTION <savepoint>

…

INSERT INTO <table> SELECT * FROM inserted — here the row is actually added

…

IF <error condition>

BEGIN

/* the next two statements replace ROLLBACK TRIGGER */

ROLLBACK TRANSACTION <savepoint>

RETURN

END

…

END

In this case, the savepoint is set inside the trigger, and the code works for any ad-hoc inserts into the <table>. Thus any application code that inserts rows into <table> does not need to be modified. This last solution looks the best, but it requires more development efforts than the other methods.

Written by Yuri Rusakov

Tagged as Migration, ROLLBACK TRIGGER, Sybase + Categorized as Newsletters, Newsletter #1, November 5, 2008