Mysql triggers - capture each column change

Question

I am trying to create trigger, that capture changes in database after update.

Table my_table I am watching:

Table my_table_log where I am writing changes to log them

And here is trigger so far:

CREATE TRIGGER `log_update` 
AFTER UPDATE ON `my_table` 
FOR EACH ROW 
BEGIN
INSERT INTO 
`my_table_log` 
(
    `id`, 
    `action`, 
    `column_name`, 
    `value_before`, 
    `value_after`, 
    `who`, 
    `ts`
)
VALUES
(
    NEW.id,
    'u', 
    'name',
    OLD.name, 
    NEW.name,
    user(),
    NOW()
);
END

Question: How to log each change of column ?

Problem: I am curently watching only if column name changed in my_table. And I have another trigger for column age. How to set trigger for each row and each column that was changed?

Thank you for your suggestions/code/inspirations

Answer 1

You might use ifs for every column you'd like to watch in your trigger:

create trigger `log_update` 
after update on `my_table` 
for each row
begin

if (old.name <> new.name) then
    insert into `my_table_log`
    (
        `id`, 
        `action`, 
        `column_name`, 
        `value_before`, 
        `value_after`, 
        `who`, 
        `ts`
    )
    values
    (
        new.id,
        'u', 
        'name',
        old.name, 
        new.name,
        user(),
        now()
    );
end if;

if (old.age <> new.age) then
    insert into `my_table_log`
    (
        `id`, 
        `action`, 
        `column_name`, 
        `value_before`, 
        `value_after`, 
        `who`, 
        `ts`
    )
    values
    (
        new.id,
        'u', 
        'age',
        old.age, 
        old.age,
        user(),
        now()
    );
end if;

end

But better make the insert a stored procedure to avoid redudancy:

create procedure `log_insert`
(
    id int(11),
    `action` char,
    column_name varchar(255),
    value_before varchar(255),
    value_after varchar(255)
)
begin

insert into `my_table_log`
(
    `id`, 
    `action`, 
    `column_name`, 
    `value_before`, 
    `value_after`, 
    `who`, 
    `ts`
)
values
(
    id,
    `action`, 
    column_name,
    value_before, 
    value_after,
    user(),
    now()
);

end

And call it in your trigger:

create trigger `log_update` 
after update on `my_table` 
for each row
begin

if (old.name <> new.name) then
    call log_insert
    (
        new.id,
        'u', 
        'name',
        old.name, 
        new.name
    );
end if;

if (old.age <> new.age) then
    call log_insert
    (
        new.id,
        'u', 
        'age',
        old.age, 
        new.age
    );
end if;

end

You can re-use the stored procedure to log events in your insert and delete triggers.

Make shure to use a composite primary key in your my_table_log to allow updates over several columns. I'd use at least:

primary key(id,column_name,who,ts).

Or use dedicated single column primary key to avoid varchars in your primary key for better performance.

Answer 2

One alternative is to just log the new values together with user() and now():

create table my_table_log 
(   id ...
,   name ...
,   age ...
,   action ...
,   who ...
,   ts ... )

To determine what was changed, compare with the previous row.

It is however rather expensive to determine what a row looked like at a certain point in time, you will have to find the last version before that point in time. Another model that makes this a lot easier is to keep track of begin_ts and end_ts for each row:

create table my_table_log 
(   id ...
,   name ...
,   age ...
,   action ...
,   who ...
,   begin_ts ... 
,   end_ts  ...)

The insert trigger adds a copy of the row with begin_ts = now() and end_ts = null. The update trigger updates end_ts = now() where end_ts is null and inserts a row like the insert trigger. The delete trigger updates end_ts and might add a copy together with who deleted the row. Determining what a row looked like at ts t is just a matter of where t between start_ts and end_ts