MySQL 触发“列更新"语法

2022-01-01 00:00:00 sql-update syntax mysql triggers

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

相关文章