MySQL Syntax Error on DELIMITER before CREATE TRIG

问题:

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

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

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

The trigger's code is:

$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 ;
";

回答1:

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

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

回答2:

Execute it directly in PhpAdmin and remove one redundant END

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

DELIMITER ;