1

The title nearly says it all. I would like to write a trigger that:

  • Uses a table called "audit_trail" with fields table_name, by, timestamp, operation, contents where contents is in JSON format
  • The trigger listens for update or insert on each table
  • If the table has a column called last_modified_by, then:
    • Make up a JSON version of the record updated/inserted
    • Add a record to the audit_trail table, with all relevant fields including contents which would have the JSON representation of the record updated/inserted

Is this technically possible with MySql? I really don't want to code this into the application itself, as it would be messy.

Please note that I am fully aware about the limitation about recording this info as JSON (hard to query, etc.). The only requirement my app has is that an admin must be able to see the "history" of a record, of when/who modified it.

While this is quite trivial, there are things I just cannot work out:

Things I can't work out:

  • How do you write a trigger that will get triggered on insert or update on ANY table
  • How to get the JSON version of a record
  • How to get the trigger to store the JSON onto the contents column

Ideas?

Merc
  • 16,277
  • 18
  • 79
  • 122
  • 1
    A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. - https://dev.mysql.com/doc/refman/5.7/en/triggers.html – P.Salmon Apr 07 '18 at 14:03
  • Hence my question: how do you add the same trigger for every table? – Merc Apr 07 '18 at 14:05
  • 1
    Cut and paste for every table amending the syntax as required. – P.Salmon Apr 07 '18 at 14:08
  • Alright, that's question 1 down (I guess I will write a script to do that). [I should have known better](https://stackoverflow.com/questions/17437121/can-a-mysql-trigger-be-associated-to-more-than-one-table-or-by-all-tables) Do you know about the other two questions? – Merc Apr 07 '18 at 14:11
  • Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case-sensitive. In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated. -https://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html – P.Salmon Apr 07 '18 at 14:21
  • Creating JSON Values https://dev.mysql.com/doc/refman/5.7/en/json.html – P.Salmon Apr 07 '18 at 14:23
  • I saw that page. I can't see anything about creating a JSON representation of a full row... which is what I need – Merc Apr 07 '18 at 14:23
  • You don't get the json version of a record - there isn't one , you have to build it. – P.Salmon Apr 07 '18 at 14:39
  • If you add all of your comments as an answer, I will mark it as accepted. Thanks. – Merc Apr 07 '18 at 14:50

0 Answers0