CODEWITHSUNDEEP
mysqltriggers
ethereal1m
ethereal1m

Reputation: 301

What is wrong with the following if statement inside MySql trigger

I'm building a trigger that updates a table when another table get updated. But, for some reason MySql doesn't like the if statement inside it.

The scenario is, there is a table named Group that has level label configuration that are stored in level1, level2, and level3 columns. The other table, Membership, stores member configuration with their level title.

For example if there is a member has level1 title, novice, and there's an update in Group_configuration level1 from 'novice' into 'newbie', trigger will update the member title into 'newbie'. This also applies to level2 and level3.

So my trigger code looks like this:

CREATE TRIGGER update_member_rank_label AFTER UPDATE ON `group`
FOR EACH ROW
begin
if (NEW.level1 <> OLD.level1 ) then
 UPDATE membership set level = NEW.level1 where level=OLD.level1 and gid=old.id;
else if  (NEW.level2 <> OLD.level2 ) then
 UPDATE membership set level = NEW.level2 where level=OLD.level2 and gid=old.id;
else if (NEW.level3 <> OLD.level3 ) then
 UPDATE membership set level = NEW.level3 where level=OLD.level3 and gid=old.id
end if;
end;

but I keep getting this error:

#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 5

Any ideas where do I miss?

regards

Upvotes: 0

Views: 63

Answers (2)

Amorfus
Amorfus

Reputation: 11

On line 9 add a semicolon. I believe your entire conditional is confused because of this.

Upvotes: 1

Olli
Olli

Reputation: 1738

please check that you set the delimiter to something else than ; before actually creating the trigger:

DELIMITER |
CREATE TRIGGER update_member_rank_label AFTER UPDATE ON `group`
FOR EACH ROW
begin
if (NEW.level1 <> OLD.level1 ) then
 UPDATE membership set level = NEW.level1 where level=OLD.level1 and gid=old.id;
else if  (NEW.level2 <> OLD.level2 ) then
 UPDATE membership set level = NEW.level2 where level=OLD.level2 and gid=old.id;
else if (NEW.level3 <> OLD.level3 ) then
 UPDATE membership set level = NEW.level3 where level=OLD.level3 and gid=old.id
end if;
end;

DELIMITER ;

Maybe this helps?

Upvotes: 2

Related Questions