Error in Trigger - Mysql

Asked

Viewed 392 times

0

I created the following Rigger, but it has a syntax error that I can’t 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

Trigger is this one:

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

From now on, thank you.

mysql sql trigger
  • 1

    Eai, young man. Here at [pt.so] we only accept questions in Portuguese. You can [Dit] your question and translate it to keep it here, but if you prefer you can post the same question on [so].

2 answers

1

Ola, according to the documentation of Mariadb: https://mariadb.com/kb/en/library/create-trigger/#examples

the Creation of this Rigger is incorrect, as it changes the orders of the initial parameters: In Maria DB the orders of the create parameter are reversed, with the After insertbefore 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

  • I don’t understand what the solution is?

  • inverts AFTER INSERT before the table name

  • I understood @Felipeeleiterio, but I suggest editing your answer to make that clear, without relying on analysis elsewhere. Although the Mariadb documentation link talking about it is very useful!

  • I improved the answer ... it worked ?

  • You look much better! Although I was a little inattentive, you had answered, but many others can read superficially too and not understand what I meant. Thank you!

  • Hello Felipe, I changed here and the error remains the same, I believe the error is lower, literally.

Show 1 more comment

0

Hey, guys, I got this thing down here, was missing the DELIMITER $$, with DELIMITER you can place the ';' required. also DECLARE does not have@and selects when done to store in a variable, you should use the word INTO

EX: 

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

these were the errors identified.

Browser other questions tagged mysql sql trigger

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