Keeping this as simple as possible, imagine I have a web app where a customer logs in and can update his/her details which are stored in MySQL. Just say for simplicity sakes this form has First Name and Last Name. In the Customer’s table of my MySQL database, I have CustomerNumber, FirstName and LastName. I also have an Audit table which consists of CustomerNumber, Type, PreviousValue, NewValue, and DateTime.
Now when a customer updates their details, I want to update the table and keep an audit record of what has changed which includes the old and new values. I can think of about 5 different ways of doing this in PHP but I don’t know what’s best, or what’s the “industry standard” way of doing this.
Currently I am thinking, get FirstName and LastName from MySQL, save those values into variables, then compare them [in PHP] to the new values. If no change, do nothing. If they have changed, the run an update statement, and an insert statement for the audit.
Another way would be to create a stored proc and pass the customer customer number, the type (FirstName), and the new value (Jon). The proc would then look up the old value and update if it’s required and insert an audit record. Howerver this would mean running a stored proc for every piece of data I will be storing (FirstName, LastName, PhoneNumber, PostCode, etc… and there could be a lot).
You can create a trigger on your Customers table that would automatically update your Audit table based on any fields you would like to track.
DELIMITER//
CREATE TRIGGER after_customer_update AFTER UPDATE ON Customers
FOR EACH ROW
BEGIN
IF (NEW.CustomerNumber <> OLD.CustomerNumber) THEN
INSERT INTO CustomerAudit (CustomerNumber, Type, PreviousValue, NewValue, DateTime) VALUES (CustomerNumber, "CustomerNumber", OLD.CustomerNumber, NEW.CustomerNumber, now());
ELSE IF (NEW.FirstName <> OLD.FirstName) THEN
INSERT INTO CustomerAudit (CustomerNumber, Type, PreviousValue, NewValue, DateTime) VALUES (CustomerNumber, "FirstName", OLD.FirstName, NEW.FirstName, now());
ELSE IF (NEW.LastName <> OLD.LastName) THEN
INSERT INTO CustomerAudit (CustomerNumber, Type, PreviousValue, NewValue, DateTime) VALUES (CustomerNumber, "LastName", OLD.LastName, NEW.LastName, now());
END IF;
END//
DELIMITER;
