创建触发器中的语法错误，有什么问题？ [英] syntax error in create trigger, what's wrong?

问题描述

有人可以告诉我我的代码语法有什么问题吗？ （这是我正在突出显示并且正在运行的唯一代码，因此行号应匹配）

can somebody tell me what is wrong with the syntax of my code please? (this is the exact and only code I'm highlighting and running so line numbers should match up)

CREATE TRIGGER `trg_part_upd` AFTER UPDATE ON `tbl_sub_model_eng_trans_part`
 FOR EACH ROW 
    if NEW.engine_sk = 0 and NEW.trans_sk = 0
    then
        UPDATE tbl_sub_model tsm
        INNER JOIN tbl_fct_sub_eng_trans tfset ON tsm.sub_model_sk = tfset.sub_model_sk
        INNER JOIN tbl_sub_model_eng_trans_part tsmetp ON tfset.trans_sk = tsmetp.trans_sk
        SET tsm.last_modified_date = NOW()
        WHERE tsmetp.sub_model_sk=NEW.sub_model_sk;
    end if;

我收到以下两个错误：

错误代码：1064。您的SQL语法有错误;检查与MySQL服务器版本对应的手册，以便在第9行附近使用正确的语法

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 9

错误代码：1064。您的SQL语法有错误;查看与MySQL服务器版本对应的手册，以便在第1行的'end if'附近使用正确的语法

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end if' at line 1

推荐答案

你忘了更改分隔符，所以MySQL认为你的第一个语句就是：

You forgot to change the delimiter, so MySQL thinks your first statement is this:

CREATE TRIGGER `trg_part_upd` AFTER UPDATE ON `tbl_sub_model_eng_trans_part`
 FOR EACH ROW 
    if NEW.engine_sk = 0 and NEW.trans_sk = 0
    then
        UPDATE tbl_sub_model tsm
        INNER JOIN tbl_fct_sub_eng_trans tfset ON tsm.sub_model_sk = tfset.sub_model_sk
        INNER JOIN tbl_sub_model_eng_trans_part tsmetp ON tfset.trans_sk = tsmetp.trans_sk
        SET tsm.last_modified_date = NOW()
        WHERE tsmetp.sub_model_sk=NEW.sub_model_sk;

只需在代码前添加：

DELIMITER $$

...之后：

$$

...所以MySQL可以将完整的触发器识别为单个语句。

... so MySQL can recognize the complete trigger as a single statement.

您可以更改 $$ 供您自己选择。

You can change $$ for your own choice.

官方文档在定义存储的程序部分。

这篇关于创建触发器中的语法错误，有什么问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！