What's wrong with this trigger?

Question

I'm using MySql with HeidiSql as an IDE. I have the following trigger:

BEGIN
declare blobpassed blob(50);
declare gid integer(10);
select lt.groupid into gid, GROUP_CONCAT(passed) into blobpassed from latest_tests lt
    left join testcaseresults tcr on tcr.testcaseresultsid = lt.tcrid
    left join grouping g on g.groupid = lt.groupid

    where lt.tcrid = NEW.testcaseresultsid
group by lt.groupid;

if blobpassed REGEXP '[1,]+' THEN 
update grouping g
set g.haspassed = 1
where g.groupid = gid;
END;

But it keeps saying that there is a syntax error around GROUP_CONCAT(passed) into blobpassed from latest_tests lt. Usually, those errors mean that there is something wrong before that (so, in this case, with gid). But i don't see what I'm doing wrong.

Can someone tell me what am I doing wrong? Thanks.

Are you using this code with the rest of the trigger parts something like this `CREATE TRIGGER trigget_name`... `delimiter`....`delimiter;`? — Lion, Apr 13 '12 at 20:19

score 0 · Answer 1 · answered Jul 30 '12 at 12:31

In SELECT ... INTO the "into" part is written only once, with a variable list, so the correct query would be:

select lt.groupid, GROUP_CONCAT(passed) into gid, blobpassed from latest_tests lt
    left join testcaseresults tcr on tcr.testcaseresultsid = lt.tcrid
    left join grouping g on g.groupid = lt.groupid
    where lt.tcrid = NEW.testcaseresultsid

The "into" part can also be placed after the query:

select lt.groupid, GROUP_CONCAT(passed) from latest_tests lt
    left join testcaseresults tcr on tcr.testcaseresultsid = lt.tcrid
    left join grouping g on g.groupid = lt.groupid
    where lt.tcrid = NEW.testcaseresultsid
    into gid, blobpassed

What's wrong with this trigger?

1 Answers1