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
$$
I think BEGIN is missing before DECLARE
– Guilherme Nascimento
Hi William, thank you for answering! I changed the structure and left so:
– Matheus Minguini
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.
– Loudenvier