Does a rollback inside a INSERT AFTER or UPDATE AFTER trigger rollback the entire transaction

Question

Does a rollback inside a INSERT AFTER or an UPDATE AFTER trigger rollback the entire transaction or just the current row that is the reason for trigger, and is it same with Commit ?

I tried to check it through my current projects code which uses MSTDC for transactions, and it appears as if though the complete transaction is aborted.

If a Rollback in the trigger does rollback the entire transaction, is there a workaround for to restrict it just the current rows.

I found a link for sybase on this, but nothing on sql server

Solution 1

Yes it will rollback the entire transaction.

It's all in the docs (see Remarks). Note the comment I've emphasised - that's pretty important I would say!!

If a ROLLBACK TRANSACTION is issued in a trigger:

All data modifications made to that point in the current transaction are rolled back, including any made by the trigger.

The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back. No nested triggers are fired by the execution of these remaining statements.

The statements in the batch after the statement that fired the trigger are not executed.

Solution 2

As you've already been let to know, the ROLLBACK command can't possibly be modified/tuned so that it only roll back the statements issued by the trigger.

If you do need a way to "rollback" actions performed by the trigger only, you could, as a workaround, consider modifying your trigger in such a way that before performing the actions, the trigger makes sure those actions do not produce exceptional situations that would cause the entire transaction to rollback.

For instance, if your trigger inserts rows, add a check to make sure the new rows do not violate e.g. unique constraints (or foreign key constraints), something like this:

IF NOT EXISTS (
  SELECT *
  FROM TableA
  WHERE …  /* a condition to test if a row or rows you are about
              to insert aren't going to violate any constraint */
)
BEGIN
  INSERT INTO TableA …
END;

Or, if your trigger deletes rows, check if it doesn't attempt to delete rows referenced by other tables (in which case you typically need to know beforehand which tables might reference the rows):

IF NOT EXISTS (
  SELECT * FROM TableB WHERE …
)
AND NOT EXISTS (
  SELECT * FROM TableC WHERE …
)
AND …
BEGIN
  DELETE FROM TableA WHERE …
END

Similarly, you'd need to make checks for update statements, if any.