Error creating Trigger with more than one table

Question

Error creating Trigger with more than one table

Navigation

#1 by (0 votes)

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'm even not sure if the vendas.OLD.id form is correct. The following error is returned when executing:

1064 - You have a syntax error in your SQL next to 'END' in 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

asked by anonymous 05.06.2018 / 22:45

1 answer

Uncaught TypeError: Error in Fade In Virtual column extracted from a json in MySql does not get NULL

score 0 · Answer 1

The Trigger that solved the problem, although it seems strange to me that an OLD is used alone when there are 3 tables in the UPDATE scope:

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 ;