While I am not advocating the approach being attempted here (i.e. manipulating the Transaction within the Trigger), I at least wanted to explain what was going on so that there was a clearer understanding of how Triggers work.
Here are some basic behaviors of Triggers:
- When a Trigger is executed, it is always within a Transaction. Individual DML statements will execute within a system-initiated Transaction (known as auto-commit) if a Transaction was not already active at the time that the DML statement was executed. Meaning:
@@TRANCOUNT == 0 before the DML statement, then
@@TRANCOUNT will be 1 in the Trigger
@@TRANCOUNT > 0 before the DML statement, then
@@TRANCOUNT will be the same value in the Trigger
- Unlike Stored Procedures where
@@TRANCOUNT needs to be the same before and after execution, Triggers allow for
@@TRANCOUNT being higher or lower after their execution, with one exception (see next item).
- If the Trigger ends with
@@TRANCOUNT being 0 (before the Trigger exits), then
- you will get an error stating that the Transaction ended in the Trigger and that the batch is being aborted
- the batch being aborted cannot undo / revert whatever ended the Transaction:
- If the Transaction was committed, the data will still be committed.
- If the Transaction was rolled back, those changes are gone.
- Once the Transaction ends, either by
deleted tables no longer have any rows in them. If you need anything from them after the Transaction ends, then you need to capture it into variables and/or a temp table (or table variable) before the Transaction ends.
- Triggers execute with an implied
XACT_ABORT ON. This might complicate things, even if you have a
TRY...CATCH construct. In some cases you might need to explicitly set
That said, you certainly can
ROLLBACK the Transaction within a Trigger. Simplistically, if you do either of those actions, you just need to issue a
BEGIN TRAN; before the Trigger ends so that you don't get the error about it aborting the batch. This assumes, in the case of
ROLLBACK, that you want to mask the fact that this happened instead of allowing
ROLLBACK to cancel the entire operation, which is how it is normally used within Triggers.
However, reality is not so simple:
- If there are multiple Triggers, the order in which they fire could result in different behavior. Assuming there were no more than 3 Triggers, you can control the order by setting the "first" and "last" Triggers. More than 3 Triggers will likely not be able to control the ordering of all of the Triggers.
- If there are nested Triggers, then ending the Transaction in the Trigger will could lead to unexptected behavior.
- If there is an active Transaction before the Trigger executes, then ending the Transaction in the Trigger will take control away from whatever is happening after the Trigger.
To account for the possibility of an active Transaction already existing prior to the Trigger firing, you would need to capture
@@TRANCOUNT at the top and then issue the correct number of
BEGIN TRAN;, and possibly also
COMMIT TRAN;, statements:
DECLARE @TranCount INT = @@TRANCOUNT,
@Index INT = 0;
-- For an actual COMMIT, do the following:
WHILE (@Index < @TranCount)
SET @Index += 1;
-- If either COMMIT or ROLLBACK occurs, do the following:
SET @Index = 0; -- reset in case COMMIT was processed above
WHILE (@Index < @TranCount)
SET @Index += 1;
Again, this is not recommended as it changes the nature of any existing Transaction to behave in an unexpected, non-intuitive way. The flow would end up being:
- Begin Transaction A
- Do something
- Insert into this Table
- Transaction A might be committed or rolled-back, in which case:
- Actions performed as "Do something" above are either committed or rolled-back
- Transaction B will be started
- Transaction A might be left alone, in which case there is no Transaction B
- After the Trigger exists
- If Transaction A was committed or rolled-back, it won't be possible to change that action. Transaction B will now exist, and changes made at this point will be completely disconnected from changes made previously in Transaction A.
- If Transaction A was left as active:
- Actions taken previously are still available to
- Actions taken after the Trigger ends are grouped along with actions taken before the Trigger fired as an atomic unit (this is how things usually work and hence the expected behavior).
The definitive reference on all this is Error and Transaction Handling in SQL Server by Erland Sommarskog.