MySQL触发器'列更新'语法 [英] MySQL trigger 'update on column' syntax

问题描述

我们可以在更新特定列时使用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;

我使用UPDATE OF col1, col2尝试了上面的代码.它在MySQL中不起作用.正确的语法是什么，有人可以为我指出一些示例.

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.

推荐答案

您不能指定仅在更新特定列时运行触发器(UPDATE影响整个记录)，但是您可以测试触发器中已更新的列:

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 ;

这篇关于MySQL触发器'列更新'语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！