Question Re: What low severity errors let transactions complete even though a statement didn't execute in Technology Q&A

What low severity errors let transactions complete even though a statement didn't execute

sladebe — Wed, 30 Sep 2026 21:51:31 GMT

Given a transaction batch like this:

begin transaction insert into mytable (id) values (1) insert into mytable (id) values (2) -- error occurs here insert into mytable (id) values (3) commit transaction

I'm pretty sure there are some low severity errors where, if they occur in the 2nd insert, the transaction will complete (commit) but with the row with id=2 missing (not inserted)

My question is, where is the documentation for this error severity transaction interaction?

Thanks in advance
Ben

Re: What low severity errors let transactions complete even though a statement didn't execute

Bret_Halford — Wed, 30 Sep 2026 22:52:15 GMT

Hi Ben, one place that comes to mind is the documentation for @@transtate. Very robust code will check @@transtate after each command and react according to the result. State 2 corresponds to the statement aborting without affecting the transaction.https://help.sap.com/docs/SAP_ASE/b65d6a040c4a4709afd93068071b2a76/aa7e5c47bc2b10148927d989297c8592.html?q=%40%40transtate&locale=en-US&version=LATEST

Re: What low severity errors let transactions complete even though a statement didn't execute

Bret_Halford — Wed, 30 Sep 2026 23:21:42 GMT

Hi Ben, see the various ARITHABORT options for some settings that will cause a statement to abort without causing the transaction to fail.https://help.sap.com/docs/SAP_ASE/b65d6a040c4a4709afd93068071b2a76/aa155b78bc2b10148e18fd231208083b.html?q=statement+abort&locale=en-US&version=LATEST

Re: What low severity errors let transactions complete even though a statement didn't execute

sladebe — Thu, 01 Oct 2026 14:43:10 GMT

Re: "very robust code will check @@transtate after each command and react according to the result".

Ok, that's interesting, thanks. Sort of a pain to do though

Re: What low severity errors let transactions complete ev...

sladebe — Thu, 01 Oct 2026 14:54:38 GMT

I also found this web page

Transact-SQL Users Guide -> Transactions: Maintain Data Consistency and Recovery -> Transactions in Stored Procedures and Triggers -> Errors and Transaction Rollbacks

which says (square brackets are my annotation):

Errors in data modification commands that affect data integrity [where a statement fails but doesn't abort the transaction] :

Arithmetic overflow and divide-by-zero errors (effects on transactions can be changed with the set arithabort arith_overflow command)
Permissions violations
Rules violations
Duplicate key violations

Granted, this is in a "Stored Procedures and Triggers" section, but I think it applies to transactions outside of stored procs and transactions too.

But, the behavior is slightly different in a trigger! From the same web page:

With duplicate key errors and rules violations, the trigger completes (unless there is also a return statement), and statements such as print, raiserror, or remote procedure calls are performed. Then, the trigger and the rest of the transaction are rolled back, and the rest of the batch is aborted.