Hello, Our data model has some constraints that are not enforceable at the database level. So, we resort to running some checks in application code at the end of each transaction.
By the time the checks run, the app doesn't know exactly which data (i.e. rows) were modified, so it has to check more data than is strictly necessary for a given operation. So I'm wondering about capturing the context of what changed during the DB transaction, and using that to pare down the amount of checking needed. My question is: is there a way to capture a log of changes (say, table name, primary key value, operation [insert/update/delete] + column[s] updated) only in the context of the current transaction? One approach I've seen suggested is to create a log table (or tables), and use triggers to populate the log. But it seems to me like those log tables would require frequent pruning to avoid performance issues. Would using temporary tables be a feasible solution? That seems to be functionally what I want, but maybe it's hard to write triggers that write to temp tables. Maybe a persistent log table is OK, and each transaction is responsible for deleting its own rows from it? Sounds like a lot of churn though... Any guidance is appreciated. Thanks, Stuart -- This communication and any attachments may contain confidential information and are intended to be viewed only by the intended recipients. If you have received this message in error, please notify the sender immediately by replying to the original message and then delete all copies of the email from your systems.
