MySQL db Audit Trail Trigger

Question

I need to track changes (audit trail) in certain tables in a MySql Db. I am trying to implement the solution suggested here.

I have an AuditLog Table with the following columns: AuditLogID, TableName, RowPK, FieldName, OldValue, NewValue, TimeStamp.

The mysql stored procedure is the following (this executes fine, and creates the procedure):

The call to the procedure such as: CALL addLogTrigger('ProductTypes', 'ProductTypeID'); executes, but does not create any triggers (see the image). SHOW TRIGGERS returns empty set.

Please let me know what could be the issue, or an alternate way to implement this.

    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, ',
                    'OldValue, ',
                    'NewValue'
                    ') 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 ;

Answer 1

I think you'll find this stored procedure doesn't create the triggers; it creates SQL statements to create the triggers. Squirt the output from this procedure into a file somewhere and then run it.

Looking at the output there appear to be some spurious vertical bar characters in it that could give trouble; my eyesight isn't what it might be so I can't be sure.