mysql触发器修改插入值,MySQL UPDATE触发器：插入实际更改的列的值

I'm working on a fairly simple ticket managment system. I want to keep a log for stuff that gets added, deleted, and changed.

I created three triggers, AFTER INSERT, AFTER DELETE, and AFTER UPDATE. The INSERT/DELETE triggers are straightforward, it's theUPDATE trigger I'm having problems with.

I would like to add which columns has changed in the table with their old & new values, i.e. colname changed from X to Y

The trigger I have now "works", except of course that it doesn't insert the actual values I'd like.

How do I get the value from OLD and NEW using the col_name variable?

I'm also not sure if this is the best possible way of doing this ... So if anyone has ideas on that, they're welcome too ... This trigger started out a lot simpler ...

BEGIN

DECLARE num_rows, i int default 1;

DECLARE col_name CHAR(255);

DECLARE updated TEXT;

DECLARE col_names CURSOR FOR

SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_name = 'storing'

ORDER BY ordinal_position;

OPEN col_names;

SELECT FOUND_ROWS() INTO num_rows;

SET i = 1;

SET @updated = 'Updated columns: ';

the_loop: LOOP

IF i > num_rows THEN

LEAVE the_loop;

END IF;

FETCH col_names INTO col_name;

/* So, how do I get the proper values? */

/* IF NEW.@col_name != OLD.@col_name THEN */

/*SET @updated = CONCAT(@updated, OLD.@col_name, ' changed into ', NEW.@col_name, ' ');*/

SET @updated = CONCAT(@updated, 'OLD', ' changed into ', 'NEW', ' ');

/* END IF;*/

SET i = i + 1;

END LOOP the_loop;

CLOSE col_names;

INSERT INTO `log` (`storing`, `medewerker`, `actie`, `data`)

VALUES (NEW.`id`, NEW.`medewerker`, "Storing aangepast", @updated);

END

解决方案Since usage of prepared statements here is impossible, I would suggest you to call some INSERT statements, e.g. -

IF NEW.column1 <> OLD.column1 THEN

INSERT INTO...

END IF;

IF NEW.column2 <> OLD.column2 THEN

INSERT INTO...

END IF;

...

Or try to copy all fields you need into another table.

In these cases you will avoid using cursor.