Why does this SQL Server Transaction Commit even though an Update Statement Fails
sql, transactions
Solution
No.
You would need to use `SET XACT_ABORT ON` to get the behaviour you apparently expect or add some error handling and only commit if no errors occurred or rollback otherwise.
Errors in SQL Server may abort the statement, scope, batch or connection.
In this case the error just aborts the statement then execution continues on with the next statement.
See Error Handling in SQL 2000 for more about this.
Problem
I have this transaction: ``` BEGIN TRAN; Update Job set JobTitle = 'wonderfullivin' where JobId = 1271; Update Job set LastTransactionDate = null where JobId = 1271; COMMIT; ``` In which the second statement must fail because LastTransactionDate is set to be NOT NULL. When I look at the transaction log though, I see the transaction gets committed. Shouldn't it be aborted because one of the queries failed?