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(
		'CREATE TRIGGER ', tableName, '_AU AFTER UPDATE ON ', tableName, ' FOR EACH ROW BEGIN ', 
		GROUP_CONCAT( 
			CONCAT( 
				'IF 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 ;
