1

TRIGGERs can be used to log changes to individual DB columns as described at https://stackoverflow.com/a/779250/569976 but that technique requires you have an IF statement for each column. It's not a huge issue if you're just interested in changes to one column BUT if you're interested in changes to all columns it becomes a bit more unweildy.

I can get all the column names of a table, dynamically, by querying the INFORMATION_SCHEMA.COLUMNS table. My question is... can I use that to dynamically reference the column names? Like in the TRIGGER you'd do OLD.columnName <> NEW.columnName but I don't think you can really make a column name dynamic like that.

In PHP you could use variable variables. eg. $obj->$var. But if MySQL has anything remotely similar that'd be news to me.

Any ideas? Or am I just going to go with the old fashioned approach of writing an IF statement for each of the 100s of columns this table has?

neubert
  • 15,947
  • 24
  • 120
  • 212
  • If it's enabled, MySQL's [binary log](https://dev.mysql.com/doc/refman/5.7/en/binary-log.html) has all the information about what is changed. Could you use that? – Tangentially Perpendicular Jul 03 '21 at 21:31
  • of course it such a thing like you can see it here https://stackoverflow.com/questions/68239599/using-loop-and-cursor-in-same-mysql-procedure-showing-error/68240123#68240123 this has loop and all and wyou would need dynamic sql for that as well, but you are locking for an **AUDIT** extension, which makes that automatiocally – nbk Jul 03 '21 at 21:42

1 Answers1

2

The trigger can only reference identifiers directly. You can't use a variable or an expression to name an identifier.

That would require dynamic SQL with PREPARE and EXECUTE so you could have the statement parsed at runtime from a string, but you can't PREPARE a new statement inside a trigger, because the trigger is already executing in the context of the currently executing statement.

The simplest solution is to write a trigger that references each column directly, with as many IF statements as there are columns in the table (I wonder why you have hundreds of columns in your table; that sounds like a different problem of bad design).

The comments above mention a binary log parser. Debezium is an example of an open-source binlog parser.

MySQL also supports an audit plugin architecture, but frankly the existing implementations of audit plugins are pretty clumsy.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828