syntax error in create trigger, what's wrong?

Question

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

Álvaro González · Accepted Answer · 2021-11-09 15:49:02Z

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.

Please note that this issue only affects MySQL clients that can accept more than one statement at once so they implement a separator to tell them apart. DELIMITER is a command of the mysql command-line tool; it may or may not be implemented in other clients.

Saharsh Shah · Accepted Answer · 2012-11-21 11:20:24Z

2

Try This:

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 ;

answered Nov 21, 2012 at 11:20

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Comments

Devart · Accepted Answer · 2012-11-21 12:19:35Z

1

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

If there are some statements in body, then body must be wrapped with BEGIN...END clause. In this case you may also need to use client DELIMITER command for the CREATE TRIGGER.
If you had one statement in the body, then you could use syntax without BEGIN...END, and without DELIMITER command.

answered Nov 21, 2012 at 12:19

Devart

120k23 gold badges172 silver badges189 bronze badges

Collectives™ on Stack Overflow

syntax error in create trigger, what's wrong?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related