Subject Re: [firebird-support] Knowing the previous rows
Author Walter R. Ojeda Valiente
Thank you Ann, your answer is very clear, as always.

I just was a bit curious about that possibility, which seems interesting for audit tables and more yet, users.

By design, very few UPDATEs and DELETEs there are on my databases because I don't like them. As you know each UPDATE or DELETE add garbage to the database so I try to avoid using those commands and so all works fine. Happy users, happy my employees, happy me. :-)

But I have a blog about Firebird and one reader did that question and as I don't knew the answer was curious.



On Fri, Aug 7, 2015 at 4:14 PM, Ann Harrison aharrison@... [firebird-support] <> wrote:

> On Aug 7, 2015, at 1:56 PM, 'Walter R. Ojeda Valiente' sistemas2000profesional@... [firebird-support] <> wrote:
> Well, after run GSTAT and reading the output I can see how many garbage a whole table has, but it don't shows me the "story" of a row. ┬┐How many COMMITs and how many ROLLBACKs the row with ID 1234 of the table CLIENTS had had?

Firebird doesn't track that information. I suggested that you start with gstat to figure out which tables are worth instrumenting, then instrument them.

You can track committed inserts (one per record, obviously) and updates and deletes (again, one per record) with triggers. Updates that roll back will be hard to track because Firebird cleans up rolled back changes immediately if it possibly can - and the triggered changes to internal are included in the clean-up. If you write your trigger to an external table the changes won't be rolled back, but you won't be able to tell the difference between a successful update and a failed update.

I don't have easy access to the Firebird release notes, but suspect that sometime someone added a system variable that will give you access to transaction ids. With that, and a transaction triggger that writes the terminal state of a transaction to an external table, you should be able to get the information you want.

But why? I understand caring about long strings of back versions. But why do you care about failed updates? Firebird removes them immediately unless the server (or inet-server for Classic) has crashed. Server crashes should be rare.

Good luck,