Syntax error in creating trigger, what's wrong?

Question

can someone tell me what is wrong with the syntax of my code please? (this is the exact and only code that I select and run, so the line numbers must match)

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 the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to '' on line 9

Error code: 1064. You have an error in the SQL syntax; check the manual for your version of MySQL server for the correct syntax to use next to "end if" on line 1

Answer 1

You forgot to change the delimiter, so MySQL considers your first statement:

 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 then this:

 $$ 

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

You can change $$ as you wish.

White papers provide details on this in the Defining Saved Programs section.

Answer 2

Try the following:

 DELIMITER $$ CREATE TRIGGER `trg_part_upd` AFTER UPDATE ON `tbl_sub_model_eng_trans_part` FOR EACH ROW BEGIN 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; END; $$ DELIMITER ; 

Answer 3

A trigger is a source object, it has a body - one or more internal statements.

• If there are any statements in the body, then the body should be wrapped in a BEGIN ... END clause. In this case, you may also need the DELIMITER client command for CREATE TRIGGER.
• If you have one statement in the body, you can use the syntax without BEGIN ... END and without the DELIMITER command.