I’m trying to create a Mysql Trigger but is giving syntax error

Asked

Viewed 577 times

1

CREATE TRIGGER comissao
ON tb_Pedido
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE
v_codFuncionario INT DEFAULT NULL;
DECLARE v_dataPedido DATETIME(3) DEFAULT NULL;
DECLARE v_valorComissao DECIMAL(15,4) DEFAULT NULL

IF(SELECT COUNT(*) FROM INSERTED) = 1 AND (SELECT COUNT(*) FROM DELETED) = 1 
THEN
SET v_codFuncionario = (SELECT codFuncionario FROM DELETED);
SET v_dataPedido = (SELECT dataPedido FROM DELETED);
DELETE FROM tb_Comissao WHERE dataPedido = v_dataPedido AND codFuncionario = v_codFuncionario;

SET v_codFuncionario = (SELECT codFuncionario FROM INSERTED);
SET v_dataPedido = (SELECT dataPedido FROM INSERTED);
SET v_valorComissao = (SELECT totalPedido FROM INSERTED); * 0.05
INSERT INTO tb_Comissao (codFuncionario, dataPedido, valorComissao) VALUES (v_codFuncionario, v_dataPedido, v_valorComissao);
ELSE
IF(SELECT COUNT(*) FROM INSERTED) = 1
THEN
SET v_codFuncionario = (SELECT codFuncionario FROM INSERTED);
SET v_dataPedido = (SELECT dataPedido FROM INSERTED);
SET v_valorComissao = (SELECT totalPedido FROM INSERTED); * 0.05
INSERT INTO tb_Comissao (codFuncionario, dataPedido, valorComissao) VALUES (v_codFuncionario, v_dataPedido, v_valorComissao);
ELSE
SET v_dataPedido = (SELECT dataPedido FROM DELETED);
DELETE FROM tb_comissao WHERE dataPedido = v_dataPedido AND codFuncionario = v_codFuncionario;
END IF;
END IF;
END

1064 - You have a syntax error in your SQL next to 'ON tb_Pedido

mysql phpmyadmin trigger

  • Post the error !!

  • #1064 - You have a syntax error in your SQL next to 'ON tb_Pedido

  • Arthur, when you need to add something, click "edit" below your question, and include it. So it is complete and better understood to others :]

  • Try to put CREATE TRIGGER comissao
ON nome_do_seu_banco.tb_Pedido

  • wouldn’t be the event before, so: CREATE TRIGGER comissao
BEFORE INSERT ON tb_Pedido...? See documentation https://dev.mysql.com/doc/refman/8.0/en/create-trigger.html and examples https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

  • OK I’ll see, from now mt thanks

  • @Ricardopunctual I think not. What can be is that you have to define an event only between FOR INSERT, UPDATE, DELETE... For example BEFORE UPDATE, AFTER INSERT, etc.

  • 1

    @RBZ ran a test on sqlfiddle and it doesn’t work, but following the documentation syntax works (http://sqlfiddle.com/#!9/4c89b3) that above syntax works on sql-server but in the mySql it seems that no

  • I believe I would have to replace for Insert with BEFORE or AFTER .

  • 1

    @Ricardopunctual exactly!... I was looking at the sql-server, and I got confused on this link: Mysql Basic: Triggers , I didn’t see the word "event" here CREATE TRIGGER nome momento evento. Sorry, guys, sorry.

  • Exactly, I also went to look at the documentation because Trigger is not something you create all the time, nor knew that the syntax was a little different, we are always learning :)

  • Thank you all, I followed the Mysql documentation and managed to create Trigger!

Show 7 more comments

1 answer

1


As quoted in the comments by Ricardo, its structure is incorrect in the scenario MySql.

Example of the structure:

DELIMITER $

CREATE TRIGGER Tgr_ItensVenda_Insert AFTER INSERT
ON ItensVenda
FOR EACH ROW
BEGIN
    UPDATE Produtos SET Estoque = Estoque - NEW.Quantidade
WHERE Referencia = NEW.Produto;
END$

CREATE TRIGGER Tgr_ItensVenda_Delete AFTER DELETE
ON ItensVenda
FOR EACH ROW
BEGIN
    UPDATE Produtos SET Estoque = Estoque + OLD.Quantidade
WHERE Referencia = OLD.Produto;
END$

DELIMITER ;

Example in your code:

CREATE TRIGGER comissao AFTER INSERT
ON tb_Pedido
FOR EACH ROW
BEGIN
...

Thus, you need to define the moment of Trigger’s "shooting":

BEFORE: before

AFTER: afterward

Options: INSERT, UPDATE, DELETE

Link: Mysql Basic: Triggers

Browser other questions tagged mysql phpmyadmin trigger

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