On 4/22/2019 7:19 PM, Johan Nel wrote:
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).
Hi Johan,
Yeah, I was wondering if I'd do some sort of hardcoded logic in the UPDATE/DELETE triggers for this. I wanted to get the logged-in User's ID in the history table so we know who changed it. Was thinking of creating a Session variable upon establishing connection to the MySQL (MariaDB) database and then referencing that @Session.MyUserID in the UPDATE/DELETE trigger code. See any problems with that approach?
--- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus