CODEWITHSUNDEEP
transactionssap-ase
riyaB
riyaB

Reputation: 337

Can a chained transaction be implemented within an unchained transaction in Sybase ASE?

Say there are 2 tables: Table test and Table test_copy. I have an insert trigger on Table test which insert in Table test_copy and I have a stored proc for inserting in Table test.

I am doing some bulk insert in test through the stored proc. Say there are 7 records. If there is an error in the 6th record, I want the entire transaction in test to rollback but test_copy to hold 5 records.

How to achieve this in Sybase ASE? Is this achievable if I declare the trigger in chained mode and the stored proc in unchained mode?

Upvotes: 0

Views: 610

Answers (1)

markp-fuso
markp-fuso

Reputation: 35106

Operations performed within a trigger are considered part of the same transaction that modified the parent table (that the trigger is defined on).

If you insert records to the test table, and the test table's insert trigger performs the inserts to the test_copy table, then there's no way to separate the two sets of inserts, ie, they're treated as a single unit of work:

  • commit the inserts to test and you commit the inserts to test_copy
  • rollback the imserts to test and you rollback the inserts to test_copy

If you want to treat the two sets of inserts (1 set to test; 1 set to test_copy) as separate units of work then you'll need to perform the inserts to test_copy as a separate standalone insert statement (eg, the stored proc will have to run insert test/select from #table and insert test_copy/select from #table).

While Sybase ASE does allow for programmatically changing the transaction mode (chained vs unchained), this setting is applied at the session level and not at the transaction level; net result is that you cannot have chained and unchained transactions nested within each other.

Upvotes: 1

Related Questions