I am trying to create a Trigger in MySql but it is giving syntax error

Question

I am trying to create a Trigger in MySql but it is giving syntax error

Navigation

#1 by (1 votes)

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

asked by anonymous 12.06.2018 / 20:39

1 answer

How to create a horizontal overflow? Recover customer orders 1. Duplication ERROR

score 1 · Accepted Answer

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

Example 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
...

So, you need to set the trigger trigger time:

BEFORE : before

AFTER : then

Options : INSERT, UPDATE, DELETE

Link: MySQL Basics: Triggers