mysql Trigger for logging, find changed columns

Question

I am writing a trigger to keep track of all the changes that happens in a table. Unfortunately the table has 150+ columns and I wanted to avoid writing each column in the code (Ex. new.col1, new.col2....) and thus I wrote a following query in "after update trigger"

INSERT INTO logs SELECT *, NOW() FROM abc WHERE abc.id = NEW.Id;

This idea is causing multiple issue due to duplication of data that is not changed in update query.

In a nutshell I want to dynamically find out which columns were part of the update query and if that is not possible is there a way to iterate through all the columns of "new" row so I can dynamically compare old.@colName == new.@colName?

I have already seen Oracle PL/SQL: Loop Over Trigger Columns Dynamically, How to determine if anything changed in update trigger in t-sql and MySQL UPDATE trigger: INSERTing the values of the columns that actually changed.

The last link is the closes to what I need with only one difference, I don't want to hard code column names in following statment because I have way over 100+ columns in all the tables I am going to write similar trigger for!!

IF NEW.column1 <> OLD.column1 THEN INSERT INTO... END IF; IF NEW.column2 <> OLD.column2 THEN INSERT INTO... END IF

Answer 1

I've been doing a bit of research on this this morning and looks like I have come across much of the same search results as you. Ultimately it looks to me like there's no way to loop over all table columns and reference the corresponding old/new values. I'm settling on explicitly checking each column and then logging:

IF (NEW.fld1 <> OLD.fld1) OR (NEW.fld1 IS NOT NULL AND OLD.fld1 IS NULL) OR (NEW.fld1 IS NULL AND OLD.fld1 IS NOT NULL) THEN
 INSERT INTO `fld_audit` (`table`, `fldname`, `oldval`, `newval`)
 VALUES ("tblname", "fld1", OLD.fld1, NEW.fld1); 
END IF; 

IF (NEW.fld2 <> OLD.fld2) OR (NEW.fld2 IS NOT NULL AND OLD.fld2 IS NULL) OR (NEW.fld2 IS NULL AND OLD.fld2 IS NOT NULL) THEN
 INSERT INTO `fld_audit` (`table`, `fldname`, `oldval`, `newval`)
 VALUES ("tblname", "fld2", OLD.fld2, NEW.fld2); 
END IF; ...

I found an inkling of another solution here. In theory you could have 3 delimited lists, one for column names, one for old vals and one for new vals. You would have to explicitly reference the old and new vals, but that would be one line (easier to maintain or copy/paste to implement on other tables) and you could then loop. So in pseudo code it would look something like this:

fields_array = concat_ws(",", "fld1", "fld2");
old_vals_array = concat_ws(",", OLD.fld1, OLD.fld2);
new_vals_array = concat_ws(",", NEW.fld1, NEW.fld2);

foreach fields_array as key => field_name
     INSERT INTO `fld_audit` (`table`, `fldname`, `oldval`, `newval`)
     VALUES ("tblname", field_name, old_vals_array[key], vew_vals_array[key]);

I haven't thought this through too much. You might need to call into a stored procedure rather than set variables. But it might be worth looking into. I've spent enough time on my triggers already. Not sure I could validate (to my boss) trial and error time on a more elegant solution.

Answer 2

As ingratiatednerd already suggested, you can use CONCAT_WS to make strings out of all required values and make a single compare statement.

Perhaps the following is useful to someone:

DECLARE old_concat, new_concat text;
SET old_concat = CONCAT_WS(',', OLD.fld1, OLD.fld2, ...);
SET new_concat = CONCAT_WS(',', NEW.fld1, NEW.fld2, ...); 

IF old_concat <> new_concat
THEN
   INSERT STATEMENT
END IF;