We use temporal tables to store the history of all changes to specific tables in the db. But we also need the user who made the changes. While on db2 side the user is the application, the application stores the actual app user into a MODIFIER column, present in each such table. For insert and update this works fine, but not for deletions.

So when a row is deleted, the app can simply first modify the column to include the person who deleted it, and then remove it. However, temporal tables only store the end result of each transaction, so given as all of the above is made in the same transaction, this doesn't work. Given that it's rather difficult for us to get changes to the application side, what would be the best method to get that user id from the deletion?

Currently the best idea I could come up with is adding a trigger to each such table, which would then take the table name, PK ID's, and the user name, and add them to a third sort of "workbench table" every time something was deleted. We would then have a batch running every few minutes which would fetch each row from this workbench table, and update the relevant history table. This solution is ugly, would be performance intensive (which is precisely what we don't want), and result in a scenario where at any given time we can't be sure whether the user present in history actually deleted the records, or whether this deleter has yet to be updated and we only see the last modifier from days ago.

This scenario has been rather typical in other DB's as well so I must wonder if there's a popular solution for this that preferably didn't involve triggers or such workbench table solutions at all? Something that can be done on the DB end only? How is this usually handled in general?

While we can edit the application to make all deletions in two unique transactions (one update, one delete), we'd then have to handle the errors in case something happens between the two transactions - such as include a new field "MARKED_FOR_DELETION", and code its usage in -, and this with testing and documentation change requirements and all would simply make the change unrealistically large.

  • I don't think temporal tables are the right tool for this; as you indicated by a tag, you want audit, so use the audit facility.
    – mustaccio
    Oct 22 '21 at 14:00
  • @mustaccio, how would audit capture the application user?
    – Lennart
    Oct 22 '21 at 18:24
  • One option would be to set the CURRENT CLIENT_USERID on the connection, which will get passed to the audit facility.
    – mustaccio
    Oct 22 '21 at 19:16
  • Thanks, I suspected something like that. Out of curiosity, would a before delete trigger work on a temporal table (not at the computer now so I can't check)?
    – Lennart
    Oct 22 '21 at 19:33
  • I realise this won't work, not due to temporal, but due to that one cant update the row that is about to be deleted.
    – Lennart
    Oct 22 '21 at 22:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.