MySQL Syntax Error on DELIMITER before CREATE TRIGGER

Question

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

The method you use to send this to MySQL can only execute 1 statement at a time. `DELIMITER $$` is one statement. — juergen d, Nov 04 '13 at 16:39
You mean the PHP mysql_query function? Well, anyway, I tried to submit the query directly in phpMyAdmin and it returned other error: #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 'END' at line 26 — trebew, Nov 04 '13 at 16:45
I edited the title to fix the misspelling, but SO asked me to reword the title because is already another question with that title. — Bill Karwin, Nov 04 '13 at 18:32

score 16 · Answer 1 · edited May 23 '17 at 11:45

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

score 2 · Answer 2 · answered Nov 04 '13 at 16:47

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 ;

MySQL Syntax Error on DELIMITER before CREATE TRIGGER

2 Answers2

Linked

Related