How to log changes on update with triggers?

Question

I want to log changes (updates) on the 'users' table in a logs table.

I haven't tried but I thought about running a second query that inserts data on the logs table every time I update something in the users table.

It is a viable solution but I would have to run a query to get the old values, run another query to insert the users update and finally insert the old values in the logs.

This is how I update data on users:

$sql = "UPDATE users SET username = '$newUserName' WHERE username = '$userName'";
$res = $conn->query($sql);
if ($conn->query($sql) === true) {
    $_SESSION['username'] = $newUserName;
    echo "Username changed.";
} else {
    echo $conn->error;
}

I want to take the old value in the users table, (the primary key of the row that has been updated, called user_id), the table and field affected, and the value that actually chagned.

So for example, I update users.username in the row with test@1234.test as primary key from Alice to Bob, the row in logs will have test@1234.test as user_id, users as table and username as field, with Alice as old_value.

How can I do this with triggers?

Answer 1

On MySQL, you can use a code similar to this:

First, create the log table:

DROP TABLE IF EXISTS users_history;
CREATE TABLE users_history LIKE users; -- inherit fields from your users table
ALTER TABLE users_history DROP COLUMN id; -- drop and recreate identifying colums in users table (examples here), to recreate in a fine-tuned way
ALTER TABLE users_history DROP COLUMN identifier;
ALTER TABLE users_history  ADD COLUMN identifier VARCHAR(255) FIRST;
ALTER TABLE users_history  ADD COLUMN identifier VARCHAR(255) FIRST;
ALTER TABLE users_history  ADD COLUMN id INT UNSIGNED FIRST;
ALTER TABLE users_history  ADD INDEX (id);
ALTER TABLE users_history  ADD COLUMN action ENUM('UPDATE','INSERT','DELETE') FIRST;
ALTER TABLE users_history  ADD COLUMN user VARCHAR(255) FIRST; -- the MySQL user who perform edits
ALTER TABLE users_history  ADD COLUMN mdate TIMESTAMP FIRST;
ALTER TABLE users_history  ADD INDEX (mdate);

Then create the trigger that will populate the log table on updates.

CREATE TRIGGER users_history_update AFTER UPDATE ON users
  FOR EACH ROW BEGIN
        INSERT INTO users_history SET 
        user =  USER(), -- the MySQL user who performed the edit
        action = 'UPDATE',
        id = OLD.id, 
        identifier = OLD.identifier, 
        username = OLD.username
        -- , [...] possibly other fields you want to track
    ;
END;

This is modified (to fit the table of the OP) from a full MySQL example I wrote at https://bibus-biblio.sourceforge.net/wiki/index.php/Automatic_creation_of_an_edit_history_using_MySQL_5 (see that link for the context: creation of more tables, other triggers for INSERT and DELETE actions).