Mysql - Phpmyadmin error running Trigger

Asked

Viewed 106 times

3

Hello, when I try to run the following Rigger, I get this error:

#1064 - You have a syntax error in your SQL next to line 11

Follow the Trigger:

CREATE TRIGGER atualizacao
after update ON cliente
FOR EACH ROW
BEGIN
    DECLARE VERX VARCHAR(10);
    SET VERX=(SELECT DESCRICAO FROM versao WHERE id=NEW.id_versao);
    if(OLD.id_versao<NEW.id_versao)then
    begin
        INSERT INTO tarefa (id_cliente,id_servico,dt_cadastro,dt_inicio,dt_final,hora_inicio,hora_final,situacao,descricao,solucao,prioridade,id_projeto,tipo) VALUES(NEW.id_cliente,10,now(),now(),now(),now(),now(),2,'Atualização',CONCAT('Sistema atualizado para versão ',VERX),1,1,0);
    end;
END$$

I wonder what it could be...

mysql phpmyadmin trigger

  • Which line 11? Would be the line "END$$"?

  • Your select is not missing JOIN ? where the field WHERE id=NEW.id_version comes from ?

  • @Edvaldolucena the NEW.id_versao is the last changed id, no need for Join

  • @Ricardopunctual that’s right, line 11 is the last

  • the last line would not be END;$$?

  • @Ricardopunctual already tried, the same thing happens..

  • I believe that there instead of end; be it end if; since you didn’t close the IF

  • I actually believe that there before the INSERT nor is it necessary to open a new begin

  • @Evaldorc your Rigger does what? You didn’t detail what she does.

  • Aham, I made the changes (removing Begin and putting end if) and it worked, thank you very much!!

Show 5 more comments

1 answer

2


What is actually causing the error is the absence of the DELIMITER$$ at the beginning of the code but there are also some other errors in the TRIGGER described.

First you ended up not closing the IF and also no need to open a second BEGIN before the INSERT. Try it this way:

DELIMITER$$
CREATE TRIGGER atualizacao
after update ON cliente
FOR EACH ROW
BEGIN
    DECLARE VERX VARCHAR(10);
    SET VERX=(SELECT DESCRICAO FROM versao WHERE id=NEW.id_versao);
    if(OLD.id_versao<NEW.id_versao)then
        INSERT INTO tarefa (id_cliente,id_servico,dt_cadastro,dt_inicio,dt_final,hora_inicio,hora_final,situacao,descricao,solucao,prioridade,id_projeto,tipo) VALUES(NEW.id_cliente,10,now(),now(),now(),now(),now(),2,'Atualização',CONCAT('Sistema atualizado para versão ',VERX),1,1,0);
    end if;
END$$
  • 1

    thank you very much, really worked now!!

Browser other questions tagged mysql phpmyadmin trigger

You are not signed in. Login or sign up in order to post.