Leave some changes permanent when transaction is rolled back

[former_SQLA_member1694875]

Let's say, we have a procedure which is called in transaction, the transaction begins and ends (commits or rollbacks) outside of the procedure. A row is inserted into a logging table everytime the procedure is called.

We need to leave this inserted row permanent even when transaction is rolled back. Is it somehow possible in SA11, SA12 or SA16 to achieve this result without reorganizing the whole code?

The last pick would be to make an event and issue a TRIGGER EVENT statement with parameters to write into logging table instead of doing it in the same transaction as event would do the job in a separate connection. Are there better approaches possible currently?

NOT TRANSACTIONAL clause on permanent tables (not only on temporary) would be a nice feature for this kind of goal. 🙂

[Breck_Carter]

> NOT TRANSACTIONAL clause on permanent tables (not only on temporary) would be a nice feature for this kind of goal.

That suggestion filled me with dread at first, but that's just because (a) I'm so used to the way transactions and connections work together in SQL Anywhere and (b) I'm so used to using events to create independent transactions for just such the requirement you have.

Recognizing that NOT TRANSACTIONAL on a permanent base table would be a, well, permanent thing, and would affect all users, all transactions, all connections for all time... I can't see why not.

Presumably each single operation (INSERT, UPDATE, DELETE) would be atomic (it either works, or it doesn't, all or nothing)... like it is for temporary tables, even if a single operation affects many rows.

Implementation may or may not be easy... since the table is permanent, the data does get written to the database file, and there is checkpointing to consider (a vast impenetrable swamp of implementation details 🙂

But, logically speaking, no objections here. Does anyone besides Arthoor want it?

[VolkerBarth]

But, logically speaking, no objections here. Does anyone besides Arthoor want it?

I don't know - however, if so, that would better be asked as a new "product suggestion" question, methinks...

[VolkerBarth]

I guess the answer is still "You can use events for that, as they run in a separate connection (and therefore in a separate transaction)" - cf. these FAQs:

• What is the best way to simulate Oracle Autonomous Transactions in SQL Anywhere?
• AUTONOMOUS_TRANSACTION in SQL Anywhere?

When you are using application code to call the procedure, you might simply use a second connection to store the logging information (that's a method we've used for logging some kind of errors).

---

NOT TRANSACTIONAL clause on permanent tables (not only on temporary) would be a nice feature for this kind of goal. 🙂

That sounds compelling, however, I would not dare to ask what that would mean for the ACID principles... - Another approach might be to log into a temporary not transactional table and move that contents later (whenever that would be) to a permanent table. That could be done

• for the current connection when you expect it to finish in a controlled fashion, so it can do its house-keeping itself or
• (when you use a global temporary table with "not transactional share by all") by a "DISCONNECT" event that would move the "log contents" of (even unexpectedly) disconnected connections to a permanent table.

However, both approaches are not fail-safe, apparently, as the logging is delayed, and the database could crash in-between. So a second parallel transaction seems more appropriate IMHO.