mysql触发器中case语句,MYSQL在触发器上的SELECT CASE中调用存储过程

im stuck on calling stored procedures inside a SELECT CASE on a Trigger, it gaves me the following error:

[Err] 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 'empata(NEW.eqvis))

WHEN 'loc' THEN pierde(NEW.eqvis)

WHEN 'vis' THEN g' at line 16

Here is the code:

DELIMITER |

CREATE TRIGGER updpartido AFTER UPDATE ON partidos

FOR EACH ROW

BEGIN

SET @vgls = vgoles(NEW.eqvis);

SET @lgls = vgoles(NEW.eqloc);

SET @vglsec = vgolesec(NEW.eqvis);

SET @lglsec = vgolesec(NEW.eqloc);

SELECT CASE

WHEN @vgls=@lgls THEN "emp"

WHEN @vgls>@lgls THEN "loc"

WHEN @vgls

END

INTO @st;

SELECT CASE @st

WHEN 'emp' THEN CALL empata(NEW.eqvis)

WHEN 'loc' THEN CALL pierde(NEW.eqvis)

WHEN 'vis' THEN CALL gana(NEW.eqvis)

END

INTO @dat;

SELECT CASE @st

WHEN 'emp' THEN CALL empata(NEW.eqloc)

WHEN 'vis' THEN CALL pierde(NEW.eqloc)

WHEN 'loc' THEN CALL gana(NEW.eqloc)

END

INTO @dat2;

UPDATE equipos SET gf=@vgls,gc=@vglsec WHERE id=NEW.eqvis;

UPDATE equipos SET gf=@lgls,gc=@lglsec WHERE id=NEW.eqloc;

END;

|

But if i remove the "CALL" the Triggers adds but when i do some update it gives me the error of "FUNCTION not found" since i made them as stored procedures and not as functions because im not going to return nothing...

Any help is very appreciated!

解决方案

You can convert your procedures empata, pierde, and gana into functions and use as coded in the first example below:

-- works

SET @st = 'loc';

SELECT CASE @st

WHEN 'loc' THEN function_(@st)

END

INTO @dat;

SELECT @dat;

I tested the following scenarios and they didn't work:

-- won't work

SET @st = 'loc';

IF @st = 'loc' THEN

function_(@st);

END IF;

-- won't work

SET @st = 'loc';

SELECT CASE @st

WHEN 'loc' THEN CALL stored_procedure_(@st)

END

INTO @dat;

SELECT @dat;

-- won't work

SET @st = 'loc';

IF @st = 'loc' THEN

CALL stored_procedure_(@st);

END IF;

At the very least, they didn't work for me.