On 2019/04/22 21:33, MB Software Solutions, LLC wrote:
VFP9SP2 app, MariaDB 10 (MySQL) backend.
One of my clients asked about a history of price changes. Easy enough to implement programmatically for the few price fields, but then I got to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long term EASIER) solution, whereby my app would query the "history" table for changes.
Your thoughts for tracking price (or other) changes?
Well I use PostgreSQL and my believe is that updates should be handled by the DB itself, e.g. an administrator doing updates directly to the DB without using the UI application... All my tables have INSERT/UPDATE columns: usr_ins, usr_upd, dt_ins, dt_upd that are trigger populated based on the login details. When rows are updated/deleted, the old row is TRIGGER driven to be inserted in a <tablename>_audit with the same columns as the live table plus an "audit_no" and "trg_op"="U|D".
Very easy then to have a view of db changes and even do rollbacks to a certain Point in time (which will even be logged in the audit table with no software interaction needed).
Johan Nel George, South Africa
FOX member : Friend(s) of XSharp X# Website : https://xsharp.info