CREATE TRIGGER 之前 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 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的DELIMITER $$ CREATE TRIGGER FP_PAGO_AI AFTER INSERT ON FORMA_PGTO FOR EACH R"附近使用的正确语法

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

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