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

Navigation
0

I'm doing a TRIGGER in MySQL so that at the time of insertion into the "Sales_Items" table the "sold" field in the product table is changed to "yes". As a sale can have several items, I decided to work with a cursor (I have never used before). I made the cursor after some searches and, when trying to execute it, the DBMS (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 @venda INT' at line 3 ". I do not know where I'm going wrong in syntax. Could someone tell me where I'm wrong in the syntax ... if so, are there any more bugs?

Thanks for responding, Guilherme! After your response, I have refaced the structure of the TRIGGER. 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 @ INT SALE, @codigo_producto int , cursor_itens CURSOR 'at line 3 " The new trigger: 

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
 $$
    
asked by anonymous 20.10.2016 / 01:56

1 answer

0

The loop in MySql does not use @@ FETCH_STATUS as in SQL Server. In the same way triggers after insert are defined otherwise. It looks like it will be a lot simpler than your code with a cursor: 

CREATE TRIGGER atualiza_produto 
AFTER INSERT 
ON itens_venda FOR EACH ROW
BEGIN

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

END

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

    
20.10.2016 / 03:39
Error with angle intercerptors Row.Cell is coming empty