Geek asks and answers

Mysql Trigger for logging, searching for modified columns

I am writing a trigger to track all changes that occur in a table. Unfortunately, the table has 150 + columns, and I would like not to write each column in the code (example. New.col1, new.col2 ....), and so I wrote the following query in "after starting the update"

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

This idea causes a multiple problem due to duplication of data that does not change in the update request.

In a nutshell, I want to dynamically find out which columns were part of the update request, and if this is not possible, there is a way to iterate over all the columns of the “new” row so that I can dynamically compare the old ones. @colName == new. @colName?

I have already seen Oracle PL / SQL: cyclic trigger columns dynamically . How to determine that something has changed when you started the update in t-sql and MySQL UPDATE: INSERT the values ​​of the columns that actually changed .

The last link is what I need with only one difference, I don’t want to hard-code the column names in the next status, because I have more than 100 columns in all the tables that I am going to write a similar trigger for !!

IF NEW.column1 <> OLD.column1 THEN INSERT INTO... END IF; IF NEW.column2 <> OLD.column2 THEN INSERT INTO... END IF
+5
source share
2 answers

, , , , . , / . , :

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; ...

. 3 , , vals vals. vals, ( / ), . , :

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]);

. , , . , , . . , ( ) .

+2

, CONCAT_WS .

, - :

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;
+1

More articles:


All Articles