MySQL db Audit Trail Trigger

Posted by Natkeeran on Stack Overflow See other posts from Stack Overflow or by Natkeeran
Published on 2010-05-06T21:16:46Z Indexed on 2010/05/06 21:28 UTC
Read the original article Hit count: 254

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 ;

alt text

© Stack Overflow or respective owner

Related posts about mysql

Related posts about triggers