MySQL trigger 'update on column' syntax

Question

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.

Answer 1

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 ;