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 ;
© Stack Overflow or respective owner