Error creating Trigger with more than one table

Asked

Viewed 41 times

0

I’m having trouble creating a Trigger that modifies more than one table.

DROP TRIGGER IF EXISTS vendedor_pago;
DELIMITER $$
CREATE TRIGGER vendedor_pago AFTER UPDATE ON parcelas
FOR EACH ROW
BEGIN
    UPDATE vendas, comissao, parcelas
        SET vendas.`conf_pagamento` = 1,
        comissao.`pago` = IF(parcelas.`parcelas_pagas` = parcelas.`num_parcelas`, 1, 0) 
    WHERE parcelas.`id_venda` = comissao.`id_venda`
        AND comissao.`id_venda` = vendas.OLD.`id`
        AND vendas.`id` = parcelas.`id_venda`
END$$
DELIMITER ;

I don’t even know if the shape vendas.OLD.id is correct. The following error is returned when executing:

1064 - You have a syntax error in your SQL next to 'END' on the line 10

I would also like to know if the FOR EACH loop is really necessary, since I want to change only one record of each table.

mysql sql trigger

  • You forgot the BEGIN and END

  • Still an error. I updated the question with the result.

  • Lack also FOR EACH ROW after the table name. Take a look at the documented examples

  • Is this really necessary when I want to update only 1 record in the table? Updating the question again.. Take a look

1 answer

0

The Trigger who solved the problem, although to me it seems strange a OLD be used alone when there are 3 tables in the scope of UPDATE:

DROP TRIGGER IF EXISTS vendedor_pago;
DELIMITER $$
CREATE TRIGGER vendedor_pago AFTER UPDATE ON parcelas
FOR EACH ROW
BEGIN
    UPDATE vendas, comissao, parcelas
        SET vendas.`conf_pagamento` = 1,
        comissao.`pago` = IF(parcelas.`parcelas_pagas` = parcelas.`num_parcelas`, 1, 0) 
    WHERE parcelas.`id_venda` = comissao.`id_venda`
        AND comissao.`id_venda` = OLD.`id`
        AND vendas.`id` = parcelas.`id_venda`;
END$$
DELIMITER ;

Browser other questions tagged mysql sql trigger

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