MySQL中的多个if语句触发 [英] Multiple if statements in MySQL trigger

问题描述

如果MySQL触发器中有多个if语句，最好的方法是什么?

If you have multiple if statements within a MySQL trigger, what is the best approach?

当前，我的SQL如下所示:

Currently my SQL looks like this:

IF NOT (NEW.status <=> OLD.status) THEN
  {my sql}

ELSEIF NOT (NEW.actual <=> OLD.actual) THEN
  {my sql}
END IF

乍一看，这似乎行得通.但是，我注意到，当多个(else)if语句为true时，只会执行第一个语句(例如，就像在PHP中一样).

On first sight this seems to work. However, I noticed that when multiple of the (else)if statements are true, only the first one gets executed (just like in PHP, for example).

如何仅使用多个if，而不必使用elseif，以便执行多于1条语句?用相同的动作创建多个触发器是不可能的，至少phpMyAdmin所示.将多个if置于一个触发器中会导致错误.

How can I just use multiple if's, not necessarily elseif's, so that more than 1 statement is executed? Creating multiple triggers with the same action is not possible, at least that is what phpMyAdmin shows. Putting multiple if's in one trigger results in errors.

推荐答案

弄清楚了.我没有使用phpMyAdmin中的可视GUI创建触发器，而是使用了普通SQL.

Figured it out. Instead of using the visual GUI in phpMyAdmin for creating triggers, I used plain SQL.

所以我用了这个SQL:

So I used this SQL:

delimiter //
create t1
after update
on my_table
for each row
    begin
        IF (NEW.status <> OLD.status) THEN
            {your sql}
        END IF;

        IF (NEW.actual <> OLD.actual) THEN
            {your sql}
        END IF;     
    end;//
delimiter ;

哪个工作正常.在phpMyAdmin GUI中查找触发器时，我注意到这是因为必须添加begin和end;.

Which works fine. When lookup the trigger up in the phpMyAdmin GUI, I noticed it is because of the begin and end; which have to be added.

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