Simple Audit Trail in MySQL

I'm working on a project which requires to have single table audit trail in MySQL 5.1.  I did something similar to that a long time ago for an SQL Server project.  I searched the Web to find something to get me started.

Hopefully I found a blog post providing some information and some SQL code on how to do this: Audit Trail.

What I like most about the solution proposed is that it uses a stored procedure and MySQL's information_schema table to generate the trigger code dynamically.  The only draw back I can see is when you add/remove columns in a table: you have to recreate the trigger using the stored procedure.

I tried to run the script in my database but it did nothing...  After fiddling a while with the code, I figured out that the stored procedure failed to generate the trigger code correctly.  The setupTest.sql and runTest.sql from the "A Little Noise" blog are working and you can use them as is.

Here is the updated code for auditTrail.sql:

DROP TABLE IF EXISTS auditLog;
CREATE TABLE `auditlog` (
  `tableName` varchar(255) default NULL,
  `rowPK` int(11) default NULL,
  `fieldName` varchar(255) default NULL,
  `old_value` blob,
  `new_value` blob,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
) ENGINE=ARCHIVE;

DROP PROCEDURE IF EXISTS addLogTrigger;
DELIMITER $
CREATE PROCEDURE addLogTrigger(IN tableName VARCHAR(255), IN pkField VARCHAR(255))
BEGIN

	SELECT CONCAT(
    'DELIMITER $\n', 'CREATE TRIGGER ', tableName, '_AU AFTER UPDATE ON ', tableName, ' FOR EACH ROW BEGIN ',
		GROUP_CONCAT(
			CONCAT(
          'IF NOT( OLD.', column_name, ' <=> NEW.', column_name, ') THEN INSERT INTO auditLog (',
					'tableName, ',
					'rowPK, ',
					'fieldName, ',
					'old_value, ',
					'new_value'
					') VALUES ( ''',
					table_name, ''', NEW.',
					pkField, ', ''',
					column_name, ''', OLD.',
					column_name, ', NEW.',
					column_name,
				'); END IF;'
			)
			SEPARATOR ' '
			), ' END;$'
		) 
		FROM 
			information_schema.columns 
		WHERE 
			table_schema = database()
			AND table_name = tableName;
END$
DELIMITER ;