为什么这个触发器不能按我想要的方式工作？

发布于 2024-11-07 19:33:05 字数 616 浏览 0 评论 0原文

我的 mysql 数据库中有两个表：顾问：id 学生：advisorID | 我想更新学生

，其中顾问中删除的 ID 等于顾问 ID，并使用一组 if 语句根据该学生的专业更改该学生的顾问 ID 值。这就是我到目前为止所得到的：

    delimiter //
    create trigger advisor_delete after delete
    on advisor
        UPDATE Student
            IF Student.major = 'major1' THEN SET Student.advisorID = 1;
            ELSEIF Student.major = 'major2' THEN SET Student.advisorID = 2;
            ELSEIF Student.major = 'major3' THEN SET Student.advisorID = 3;
            ELSE SET Student.advisorID = 4;
            ENDIF;
        WHERE Student.advisorID = OLD.id;
    end//

欢迎任何帮助。谢谢。

原文

I have two tables in my mysql database:
Advisor: id
Student: advisorID | major

I want to update Student where the deleted ID in Advisor is equal to advisorID and use a set of if statements to change that student's advisorID value based on that student's major. This is what I have thus far:

    delimiter //
    create trigger advisor_delete after delete
    on advisor
        UPDATE Student
            IF Student.major = 'major1' THEN SET Student.advisorID = 1;
            ELSEIF Student.major = 'major2' THEN SET Student.advisorID = 2;
            ELSEIF Student.major = 'major3' THEN SET Student.advisorID = 3;
            ELSE SET Student.advisorID = 4;
            ENDIF;
        WHERE Student.advisorID = OLD.id;
    end//

Any help is welcome. Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

风铃鹿 2024-11-14 19:33:05

我不知道这个 UPDATE/IF ELSEIF 语法，但 CASE 语句适合您：

delimiter //
create trigger advisor_delete after delete
on advisor
    UPDATE Student
        SET Student.advisorID = 
            CASE Student.major WHEN 'major1' THEN 1
                               WHEN 'major2' THEN 2
                               WHEN 'major3' THEN 3
                               ELSE 4
            END
    WHERE Student.advisorID = OLD.id;
end//

I don't know this UPDATE/IF ELSEIF syntax, but CASE statement will work for you:

delimiter //
create trigger advisor_delete after delete
on advisor
    UPDATE Student
        SET Student.advisorID = 
            CASE Student.major WHEN 'major1' THEN 1
                               WHEN 'major2' THEN 2
                               WHEN 'major3' THEN 3
                               ELSE 4
            END
    WHERE Student.advisorID = OLD.id;
end//

回复收藏 0 原文

~没有更多了~