MYSQL在触发器上的SELECT CASE中调用存储过程 [英] MYSQL Calling stored procedures inside an SELECT CASE on a Trigger

问题描述

im停留在触发器上的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

这是代码:

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<@lgls THEN "vis" 
        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;

|

但是，如果我删除了"CALL"，触发器会添加，但是当我进行一些更新时，它会给我"FUNCTION not found"的错误，因为我将它们设置为存储过程而不是函数，因为我不会返回任何内容. ..

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...

非常感谢您的帮助！

推荐答案

您可以将过程empata，pierde和gana转换为函数，并按照下面第一个示例中的代码进行使用:

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.

这篇关于MYSQL在触发器上的SELECT CASE中调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！