Geek asks and answers

Rolls back inside INSERT AFTER or UPDATE AFTER trigger rolls back the entire transaction

Is the rollback performed inside the INSERT AFTER or UPDATE AFTER trigger of the entire transaction, or only the current row that is the cause of the trigger and matches Commit?

I tried to check it with the current projects code, which uses MSTDC for transactions, and it seems that if the complete transaction is aborted.

If the rollback in the trigger rolls back the entire transaction, is there a way around the path to limit only the current lines.

I found a link for sybase but nothing on sql server

+5
source share
3 answers

Yes, this is a rollback of the entire transaction.

docs (. ). , - , !

ROLLBACK TRANSACTION :

, , , .

ROLLBACK. - , . .

, .

+7

, ROLLBACK / , , .

"" , , , , , , , .

, , , , , . ( ), - :

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;

, , , , ( , , , ):

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

, , .

+1

Any rollback command rolls back until @@ trancount is 0 unless you specify some savepoints and it doesn't matter where you put the rollback command tran.

The best way is to study the code again and confirm the business requirements and see why you need a rollback at startup?

0
source

More articles:


All Articles