SAP ASE Implicit Rollback in External Transactions

If you encounter errors in an external transaction (for example, deadlocks, aborted update triggers, and so on) , SAP ASE may abort the external transaction.

Although SAP ASE sends error messages for failures, applications do not always check for messages, particularily for simple inserts (for example, they may not be aware of triggers added by DBAs). It may not always be obvious from the error messages that the XA transaction has ended.

If SAP ASE aborts an external transaction and throws a SQLException, you can issue select @@trancount. If the value for @@trancount is zero, the DTM transaction was aborted.

The application should call the transaction manger (typically an application server) notifying it that the transaction aborted. If you ignore error messages, subsequent updates could take place outside the DTM transaction context (for example, local transactions). You can log the error messages and check the @@transtate or @@trancount to verify the updates occured.

The following describes a trigger that causes SAP ASE to rollback an external transaction. The insert statement contains the trigger that can potentially fail. If SAP ASE cannot issue the insert, the update runs the ut.commit function

This example (in pseudo code) assumes you are running a JTA/XA UserTransaction:
try {

insert into table values (xx.... )
update table
ut.commit();

} catch (SQLException sqe) {

if this is a known error then process 
else   
select @@trancount into count
if count == 0 
then ut.rollback() } 

If you do not include the rollback funtion, then additional updates take place outsie the JTA/XA transaction.