MySQL触发条件更新 [英] MySQL trigger with conditions on UPDATE

问题描述

是否可以在mysql触发器中插入条件类型的更新?如果我需要在某种类型的更新之后执行某些操作，例如，如果更新是某种类型的字段值更改，则该操作是必需的.最好说，如果在我原始表中的特定字段将其值更改为特定值之后，需要在另一个表中增加一个值，可以吗?任何帮助将不胜感激.
我正在尝试:

Is it possible to insert a conditional type of update in a mysql trigger? If I need to do something after a certain type of update, for example if the update is a certain type of changing in field value. Better said, if I need to increment a value in another table after that a specific field in my original table changes his value to a specific value, is it possible? Any help will be greatly appreciated.
I'm trying this:

CREATE TRIGGER Increment 
AFTER UPDATE ON Subscription
FOR EACH ROW
BEGIN
UPDATE USER
SET num_sub=num_sub+1
IF NEW.State <> OLD.State AND NEW.STATE='C'
END IF
END

但是它不起作用，语法错误.我通过phpmyadmin插入触发器.

But it doesn't work, syntax error. I insert the trigger via phpmyadmin.

推荐答案

尝试如下操作:

CREATE TRIGGER Increment 
AFTER UPDATE ON Subscription
FOR EACH ROW
BEGIN
    IF NEW.State <> OLD.State AND NEW.STATE='C' THEN
        UPDATE USER
        SET num_sub=num_sub+1
        WHERE USER.id = NEW.user_id; -- change this condition as you need
    END IF;
END

这也应该起作用:

CREATE TRIGGER Increment 
AFTER UPDATE ON Subscription
FOR EACH ROW
    UPDATE USER
    SET num_sub=num_sub+1
    WHERE USER.id = NEW.user_id
      AND NEW.State <> OLD.State
      AND NEW.STATE='C';

注意: NEW 和 OLD 是指 Subscription 表中正在更新的行. OLD 包含更新之前的值. NEW 包含更新后的值.因此，要检查 state 是否已更改，可以比较 NEW.State<>OLD.State .

Note: NEW and OLD refer to the row from the Subscription table which is being updated. OLD contains the values before the update. NEW contains the values after the update. So to check if the state has been changed you can compare NEW.State <> OLD.State.

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