mysql触发器中使用new报语法错误,创建触发器中的语法错误，有什么问题？

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;

I get these two errors:

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

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

解决方案

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;

Just add this before the code:

DELIMITER $$

... and this afterwards:

$$

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

You can change $$ for your own choice.

Official docs give details on this in the Defining Stored Programs section.