mysql触发器 update,MySQL触发器'列更新'语法

Can we use a MySQL trigger on update of specific columns, which updates another column in same table (same row)

create trigger my_trigger

BEFORE UPDATE OF col1, col2 ON TABLE_NAME

for each row

set NEW.col3 = NEW.col3 +1;

I tried the above code using UPDATE OF col1, col2 . It is not working in MySQL. What is the correct syntax, can somebody point me to some examples.

解决方案

You can't specify that the trigger is to be run only on the update of specific columns (an UPDATE affects the entire record), but you can test which columns have been updated within your trigger:

DELIMITER ;;

CREATE TRIGGER my_trigger BEFORE UPDATE ON TABLE_NAME FOR EACH ROW

IF NOT (NEW.col1 <=> OLD.col1 AND NEW.col2 <=> OLD.col2) THEN

SET NEW.col3 = NEW.col3 + 1;

END IF;;

DELIMITER ;