How to group data changes by operation with MySQL triggers

Question

I am using triggers in MySQL to log changes to the data. These changes are recorded on a row level. I can now insert an entry in my log table for each row that is changed. However, I also need to record the operation to which the changes belong.

For example, a delete operation like "DELETE * FROM table WHERE type=x" can delete multiple rows. With the trigger I can insert an entry for each deleted row into the log table, but I would like to also provide a unique identifier for the operation as a whole, so that the log table looks something like:

log_id  operation_id  tablename  fieldname  oldvalue  newvalue

1       1             table      id         1         null  
2       1             table      type       a         null
3       1             table      id         2         null
4       1             table      type       a         null
5       2             table      id         3         null  
6       2             table      type       b         null
7       2             table      id         4         null
8       2             table      type       b         null

Is there a way in MySQL to identify the higher level operation to which the row changes belong? Or is this only possible by means of application level code? In the future it would also be nice to be able to record the transaction to which an operation belongs.

Another question is if it is possible to capture the actual SQL query, besides using the query log. I don't think so myself, but maybe I am missing something. It is of course possible to capture these at the application level, but the goal is to keep intrusions to the application level code as minimal as possible.

When this is not possible with MySQL, how is this with other database systems? For the current project it is not an option to use something other than MySQL, but it would be nice to know for future projects.

Answer 1

Based on the creation of a trigger, you know what some of the context: delete,insert,update.

CREATE TRIGGER  <name_of_trigger>  
   [BEFORE | AFTER] 
   [UPDATE | INSERT | DELETE] -- Dictates context for trigger 'action'.
ON <table_name>
FOR EACH ROW BEGIN
  <SQL to execute>
END

If you add an 'action' column to your log, you can then tell what action was used to cause a record to be inserted into the 'change' log.

Example:

DELIMITER $$

-- Create Update,Insert,Delete triggers that logs the result of a table update to a log  
CREATE TRIGGER  trg_AFT_INS_table  AFTER UPDATE ON table
FOR EACH ROW BEGIN
  INSERT INTO table_log ( a, b, c, d, action)
  VALUES ( NEW.a, NEW.b, NEW.c, NEW.d, "update");
END $$
CREATE TRIGGER  trg_AFT_INS_table  AFTER INSERT ON table
FOR EACH ROW BEGIN
  INSERT INTO table_log ( a, b, c, d, action)
  VALUES ( NEW.a, NEW.b, NEW.c, NEW.d, "insert");
END $$
CREATE TRIGGER  trg_AFT_INS_table  AFTER DELETE ON table
FOR EACH ROW BEGIN
  INSERT INTO table_log ( a, b, c, d, action)
  VALUES ( OLD.a, OLD.b, OLD.c, OLD.d, "delete");
END $$

You could get into discussion of the BEFORE vs AFTER when logging table modifications... We found that using the 'after' values makes the log effectively the same as a 'binary log' and can be used to reconstruct, inclusive of the last modification, the table on which the logging is for.

-- J Jorgenson --