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

[Bret_Halford]

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

[Bret_Halford]

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....

[Bret_Halford]

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....

[sladebe]

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

[sladebe · Accepted solution]

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.