Microsoft® SQL Server 2012 Unleashed

Using Savepoints in Triggers

Although BEGIN TRAN statements are not recommended within a trigger, you can set a savepoint in a trigger and roll back to the savepoint. This technique rolls back only the operations within the trigger subsequent to the savepoint. The trigger and transaction it is a part of are still active until the transaction is subsequently committed or rolled back. The batch continues processing.

Savepoints can be used to avoid a trigger’s arbitrarily rolling back an entire transaction. You can roll back to the named savepoint in the trigger and then issue a raiserror and return immediately to pass the error code back to the calling process. The calling process can then check the error status of the data modification statement ...