Error in trigger - MySql

Question

Error in trigger - MySql

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I created the following trigger, but it has a syntax error that I can not find.

The error is as follows

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'on rev_reviews AFTER INSERT AS BEGIN DECLARE @totalItens int DECLARE @revis' at line 1

the trigger is this:

CREATE TRIGGER insertReview 
on rev_reviews
AFTER INSERT 
AS
BEGIN
    DECLARE @totalItens int
    DECLARE @revisado varchar(4)
    DECLARE @countRevisado int
    DECLARE @ean varchar(4)
    DECLARE @countEan int


    select count(1) into @totalItens from rev_reviews where task_id = new.task_id
    select @revisado = revisado, @ean = ean_existe from rev_reviews where id = new.id    

    select @countRevisado = count(1) from rev_reviews where task_id = new.task_id AND Revisado LIKE @revisado

    select @countEan = count(1) from rev_reviews where task_id = new.task_id AND ean_existe LIKE @ean;

    CASE
        WHEN @ean == 'Sim' THEN
            CASE
                WHEN @revisado == 'Nao' 
                    THEN update rev_tasks set totalItens = @totalItens, totalPen = @countRevisado, totalEan = @countEan where id = new.task_id
                WHEN @revisado == 'Sim' 
                    THEN update rev_tasks set totalItens = @totalItens, totalRev = @countRevisado, totalEan = @countEan where id = new.task_id
                WHEN @revisado == 'NR' 
                    THEN update rev_tasks set totalItens = @totalItens, totalNr = @countRevisado, totalEan = @countEan where id = new.task_id
                WHEN @revisado == 'Aut' 
                    THEN update rev_tasks set totalItens = @totalItens, totalAut = @countRevisado, totalEan = @countEan where id = new.task_id
            END
        ELSE
            CASE
                WHEN @revisado == 'Nao' 
                    THEN update rev_tasks set totalItens = @totalItens, totalPen = @countRevisado, totalNEan = @countEan where id = new.task_id
                WHEN @revisado == 'Sim' 
                    THEN update rev_tasks set totalItens = @totalItens, totalRev = @countRevisado, totalNEan = @countEan where id = new.task_id
                WHEN @revisado == 'NR' 
                    THEN update rev_tasks set totalItens = @totalItens, totalNr = @countRevisado, totalNEan = @countEan where id = new.task_id
                WHEN @revisado == 'Aut' 
                    THEN update rev_tasks set totalItens = @totalItens, totalAut = @countRevisado, totalNEan = @countEan where id = new.task_id
            END
    END
END

Thanks in advance.

mysql sql trigger

asked by anonymous 13.11.2017 / 20:18

2 answers

Download all files from a remote FTP directory Problem with DateTime field in ASP.NET MVC POST

score 1 · Answer 1

Hello, according to the MariaDB documentation: link

Creation of this trigger is incorrect, as it changes the orders of the initial parameters: In Maria DB the parameters of the create parameter are inverted, with After insert before the table name and not after as in most banks

///seu
CREATE TRIGGER insertReview 
on rev_reviews
AFTER INSERT 
AS
///correto
CREATE DEFINER='root'@'localhost' TRIGGER insertReview 
  AFTER INSERT ON rev_reviews

score 0 · Answer 2

I was able to solve this problem here, was missing the DELIMITER $$, with the DELIMITER you can put the ';' required. in addition the DECLARE does not have the '@' and the selects when done to save in a variable, one must use the word INTO

EX:

 select count(1) into @totalItens from rev_reviews where task_id = new.task_id

these were the errors identified.