October 25, 2007 at 5:32 pm
Hi All
I have an insert trigger in a table and I found something very interesting. When I insert a new record but when error occurs in the trigger. No data is inserted. However, the identity value increases.
Is this meant to happen this way? I thought when trigger failed, the data is still intake.
Is that mean the data have been inserted but due to fall out from the trigger, the data have been roll back?
October 25, 2007 at 10:11 pm
triggers run as part of the same transaction as the insert statement. If the trigger fails, the transaction is rolled back.
Identities are not generated/locked by the transaction. If they were, if you had a transaction that took a long time and inserted a new record (requiring a new identity value), then that transaction would stop all other inserts into the table.
This situation is not acceptable so the generation of the next identity value is designed so that multiple transactions can simultaneously get new identity values that are unique. Hence, if one transaction fails, there is no related rollback of the identity value.
Hope that made sense
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply