I'm beginner with MySQL and I'm trying to create a log trigger that fills a log table like this:
DELIMITER $$
CREATE TRIGGER ai_user AFTER UPDATE ON user
FOR EACH ROW
BEGIN
INSERT INTO user_log (action,id,timestamp,column_name,old_value, new_value)
VALUES('update',NEW.id,NOW(),COLUMN.NAME,OLD.column_value, NEW.column_value);
END$$
DELIMITER ;
But I'm with problems to get the changed column name and it's old and new value. Any help would be appreciated. Thanks.
You have to do this the painful way, one at a time:
if not (old.col1 = new.col1 or old.col1 is null and new.col1 is null)
INSERT INTO user_log(action, id, timestamp, column_name, old_value, new_value)
VALUES('update', NEW.id, NOW(), 'col1', OLD.col1, NEW.col1);
end if;
if not (old.col2 = new.col2 or old.col2 is null and new.col2 is null)
INSERT INTO user_log(action, id, timestamp, column_name, old_value, new_value)
VALUES('update', NEW.id, NOW(), 'col2', OLD.col2, NEW.col2);
end if;
. . .
Note that these do not have else clauses. You want to check for every value.
By the way, when you do updates this way, you need to be careful about types if the columns you are comparing have different types.
