MySQL declare variable in trigger error

Question

Anybody know what's wrong with my trigger declaration?

CREATE TRIGGER `sch_trigger_curriculum_subject_before_insert` BEFORE INSERT ON  
`sch_curriculum_subject` FOR EACH ROW 
BEGIN
   DECLARE curriculum_code VARCHAR(50);        
   SET curriculum_code = SELECT code FROM sch_curriculum WHERE id=NEW.id;
   SET NEW.`code_name` = CONCAT(curriculum_code,' - ', NEW.code,' - (',NEW.name,')');
END;

The error: SQL Error(1064): You have error in you SQL syntax ..... near '' at line 4.

According to error message, there is something wrong with my curriculum_code declaration. But I cant find out what's wrong.

Thanks in advance

UPDATE: already solved, the problem is on delimiter, here is the working one

DELIMITER $$
CREATE TRIGGER `sch_trigger_curriculum_subject_before_insert` BEFORE INSERT ON  
`sch_curriculum_subject` FOR EACH ROW 
BEGIN
  DECLARE curriculum_code VARCHAR(50);        
  SET curriculum_code = (SELECT code FROM sch_curriculum WHERE id=NEW.id);
  SET NEW.`code_name` = CONCAT(curriculum_code,' - ', NEW.code,' - (',NEW.name,')');
END$$

Is curriculum_code returns multiple rows ?

nanobash
– nanobash

2012-08-21 15:51:15 +00:00
Commented Aug 21, 2012 at 15:51 — nanobash
– nanobash, Commented Aug 21, 2012 at 15:51
@Tornike, no it is not. The problem is on delimiter. Thanks

goFrendiAsgard
– goFrendiAsgard

2012-08-21 15:57:32 +00:00
Commented Aug 21, 2012 at 15:57 — goFrendiAsgard
– goFrendiAsgard, Commented Aug 21, 2012 at 15:57

eggyal · Accepted Answer · 2012-08-21 15:52:12Z

1

You can't assign a SELECT statement to a variable in that fashion. At very least you should place the SELECT statement within parentheses to execute it as a subquery, but better yet:

SELECT code FROM sch_curriculum WHERE id = NEW.id INTO curriculum_code;

Be careful that the query does not return multiple records (one assumes that there is a UNIQUE constraint on the id column, so this probably isn't an issue).

answered Aug 21, 2012 at 15:52

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

goFrendiAsgard Over a year ago

thx for your suggestion. I can make sure that the query is unique.

D'Arcy Rittich · Accepted Answer · 2012-08-21 15:52:21Z

0

You need to make sure multiple values are not returned from the SELECT.

Try:

SET curriculum_code = (SELECT code FROM sch_curriculum WHERE id=NEW.id LIMIT 1);

answered Aug 21, 2012 at 15:52

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

1 Comment

goFrendiAsgard Over a year ago

Yeah. This is one of the error reason. I'll accept your answer later. thx

Eugen Rieck · Accepted Answer · 2012-08-21 15:52:51Z

0

You can't simply SET curriculum_code = some_SQL. I suspect you mean SELECT ... INTO curriculum_code

answered Aug 21, 2012 at 15:52

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

1 Comment

goFrendiAsgard Over a year ago

Thanks for your suggestion. But I think I've found the problem

Collectives™ on Stack Overflow

MySQL declare variable in trigger error

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related