To explain with more detail the title, I’m trying to create something (a stored procedure maybe?) to save changes made to a specific table to another specific table.
The idea is to have some kind of versionning/logging/backup/….
What I had in mind was having my “real” table Table1 and my audit one Table1AuditTrail and that when changing a value in Table1, I would add a row to my second table with the timestamp, the field changed (the name of the column), the new value and the name of the user logged in the app (They all use the same SQL user…).
I don’t think triggers will work for this kind of behavior but I don’t know enough about stored procedure and SQL to know if and how this is even possible at all…
I’m using MySQL 8.0.37 and InnoDB as engine.
Arnaud is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.