MySQL db Audit Trail Trigger
- by Natkeeran
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 ;
