Mysql Trigger - Error syntax: DECLARE @sale INT' at line 3

Asked

Viewed 161 times

0

I am doing a TRIGGER in Mysql so that at the time of an insertion in the table "Itens_venda" the field "sold" in the product table is changed to "yes". As a sale can have several items, I decided to work with a cursor (never used before). I cursor after some searches and when trying to run it, SGBD (Phpmyadmin) reported the following error: "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 'DECLARE @sale INT' at line 3". I don’t know where I’m going wrong in the syntax. Could someone tell me where I’m going wrong in the syntax... if yes, there are more errors?

Thank you for answering, Guilherme! After your reply, I redid the structure of TRIGGUER. But a single error is now appearing: "#1064 - 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 'AS BEGIN DECLARE @venda INT, @codigo_produto int, cursor_itens CURSOR ' at line 3" The new trigguer:

DELIMITER $$
CREATE TRIGGER atualiza_produto 
    AFTER INSERT ON itens_venda
    BEGIN
         DECLARE @venda INT,  @codigo_produto int, cursor_itens CURSOR FOR SELECT produto_codigo FROM itens_venda WHERE id_venda = @venda
         SET @venda = ('SELECT id_venda FROM INSERTED')
         OPEN cursor_itens
            FETCH NEXT FROM cursor_itens INTO @codigo_produto
                WHILE @@FETCH_STATUS = 0
                BEGIN
                    UPDATE produto SET vendido = 's' WHERE codigo = @codigo_produto
                END
        CLOSE cursor_itens
        DEALLOCATE cursor_itens          
 END
 $$
  • 1

    I think BEGIN is missing before DECLARE

  • Hi William, thank you for answering! I changed the structure and left so:

  • I answered the question and then realized that you are using @@FETCH_STATUS which is SQL Server syntax. In Mysql it is totally different. I’ll update the answer.

1 answer

0

The loop in Mysql does not use @@FETCH_STATUS as in SQL Server. Similarly, after Insert triggers are defined differently. It seems to me that it will be much simpler than your cursor code:

CREATE TRIGGER atualiza_produto 
AFTER INSERT 
ON itens_venda FOR EACH ROW
BEGIN

    UPDATE produto SET vendido = 's' WHERE codigo = NEW.produto_codigo;

END

Trigger is called for each inserted row and you can access the new value inserted using the syntax NEW.nome_do_campo.

  • 1

    thanks for the help, buddy. worked without the cursor! ;)

  • @Matheusminguini do not forget to define the answer as the correct one, otherwise the question remains open.

Browser other questions tagged

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