在创建触发器之前，DELIMITER上的MySQL语法错误 [英] MySQL Syntax Error on DELIMITER before CREATE TRIGGER

问题描述

我正在尝试将用Firebird制作的触发器重写为MySql触发器.

I'm trying to rewrite a Trigger that I made with Firebird to a MySql Trigger.

我真的不知道会发生什么.如果有人可以帮助我...谢谢

I realy have no idea what could be. If anyone could help me... thanks

我正在使用PHP提交SQL，如下所示，错误消息是:

I'm submiting the SQL with PHP just like follows, and the error message is:

无效的查询:您的SQL语法有错误；在第1行的'DELIMITER $$ CREATE TRIGGER FP_PAGO_AI AFTER INSERT ON FORMA_PGTO FOR EACH R'中检查与您的MySQL服务器版本对应的手册以使用正确的语法

Invalid query: 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 'DELIMITER $$ CREATE TRIGGER FP_PAGO_AI AFTER INSERT ON FORMA_PGTO FOR EACH R' at line 1

触发器的代码是:

$str[] = "
DELIMITER $$

CREATE TRIGGER FP_PAGO_AU AFTER UPDATE ON FORMA_PGTO
FOR EACH ROW
BEGIN

    declare rec_count integer;
    declare pg_count integer;
    declare cp_pago integer;

    select count(*) from forma_pgto fp where fp.id_cpagar=new.id_cpagar into rec_count;
    select count(pago) from forma_pgto f where f.id_cpagar=new.id_cpagar and f.pago=1 into pg_count;

    /* Se todas parcelas estao pagas, entao setar conta paga */
    if (rec_count = pg_count) then
        update cpagar c set c.pago=1 where c.id=new.id_cpagar;
    /* Senao */
    else
        /* Se CPAGAR.PAGO = 1, recebe 0 */
        select cpg.pago from cpagar cpg where cpg.id=new.id_cpagar into cp_pago;
        if (cp_pago = 1) then /* Se cp_pago = 1 */
            update cpagar set pago=0 where id=new.id_cpagar;
        end if;
    end if;

END

END $$

DELIMITER ;
";

推荐答案

您根本不需要DELIMITER $$.这是 mysql客户端内置命令. SQL解析器无法识别客户端内置文件.

You don't need DELIMITER $$ at all. That's a mysql client builtin command. Client builtins are not recognized by the SQL parser.

您可以只将CREATE TRIGGER语句作为单个语句执行，然后在语句末尾不需要定界符.分隔符仅在支持多个语句的接口(例如mysql客户端)中很重要.

You can just execute the CREATE TRIGGER statement as a single statement and then you don't need to have a delimiter at the end of the statement. Delimiters are only important in interfaces that support multiple statements (e.g. the mysql client).

phpMyAdmin还允许多个语句，因此您确实需要设置定界符，但这是通过用户界面控件完成的，而不是DELIMITER命令.请参见在phpMyAdmin中存储过程

phpMyAdmin also permits multiple statements, so you do need to set the delimiter, but this is done with a user interface control, not the DELIMITER command. See Store procedures in phpMyAdmin

这篇关于在创建触发器之前，DELIMITER上的MySQL语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！