Deviopath › PostgreSQL

Audit Log for Balances

Hard · Triggers

An AFTER trigger runs once the change is done, which is the right moment to write a log. In the trigger function, TG_OP holds 'INSERT', 'UPDATE' or 'DELETE'. OLD is the row before the change; NEW is the row after it (NULL for a delete).

Record every balance change of accounts in account_audit:

- On UPDATE, if balance changed: insert account_id, action = 'UPDATE', old_balance and new_balance. Updates that do not change the balance (like renaming the owner) are not logged. - On DELETE: insert account_id, action = 'DELETE', old_balance, and NULL as new_balance.

The check runs a few updates and deletes and then reads account_audit.

Next: Stop Overselling