mysql - SQL 触发器链接 IF 语句

Question

我似乎无法在 SQL 中使用多个不相关的 if 语句

我有一个用户表，其中有一堆应该是唯一的列，但有些列也是可选的。由于当多个字段具有 NULL 值时 SQL 会引发错误，因此我决定使用触发器来解决该问题。

这是我的写法:

create definer = root@localhost trigger `check-if-unique-update`
    before UPDATE
    on contact
    for each row
    if new.phone2 is null
    then
        if (select count(id) from contact where phone2 = new.phone2 or phone = new.phone2) > 0
        then
            SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'There is something wrong with the Contact data you entered!';
        end if;
    end if;

    if new.email2 is not null
        then
        if (select count(id) from contact where email2 = new.email2 or email = new.email2)
            then
            SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'There is something wrong with the Contact data you entered!';
        end if;
    end if;

    if new.fax is not null
        then

        end if;
    end if;

首先，DataGrip(JetBrains 的数据库管理 IDE)给了我一个错误。当我将文件上传到 MySql 数据库时，它只显示第一个 if 语句

if new.phone2 is null
    then
        if (select count(id) from contact where phone2 = new.phone2 or phone = new.phone2) > 0
        then
            SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'There is something wrong with the Contact data you entered!';
        end if;
    end if;

我觉得我缺少一些基本细节，或者这在 sql 中是不可能的吗？

Answer 1

您是否尝试过在触发器中包含开始-结束？

create definer = root@localhost trigger `check-if-unique-update`
    before UPDATE
    on contact
    for each row
    begin
        if new.phone2 is null
        then
            if (select count(id) from contact where phone2 = new.phone2 or phone = new.phone2) > 0
            then
                SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'There is something wrong with the Contact data you entered!';
            end if;
        end if;

        if new.email2 is not null
            then
            if (select count(id) from contact where email2 = new.email2 or email = new.email2)
                then
                SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'There is something wrong with the Contact data you entered!';
            end if;
        end if;

        if new.fax is not null
            then

            end if;
        end if;
    end

根据您的 IDE，您可能需要将分隔符更改为 //。

引用:https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html