Integration between Mysql banks giving error 1442?

Question

Hello! I have the following problem to solve: bank 1 cannot interact directly with bank 3, so I need an interface that interacts between them, which I called bank 2. Seat 1 inserts into seat 2; seat 2 inserts into seat 3; seat 3 inserts into seat 2; Seat 2 inserts into seat 3. I hope that’s clear. When I do the integration between two banks (inserting from the bank 1 in the bank 2, or from the bank 3 in the bank 2) it works perfectly, but when I do the integration with the 3 banks, it gives me the error 1442:

15:27:13    INSERT INTO banco1.Clientes VALUES (42,'mariana', '1998-05-08', '48381856' '4539-6', 'ananana', 'oqe?', '162', 'sasnaush', 'qsjqijqiwj',          'SP', '13720000') Error Code: 1442. Can't update table 'clientes' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.  0.000 sec

Can anyone tell me why this mistake appears only when I integrate three banks?

Follow the two triggers (Insert and update):

INSERT

DROP TRIGGER insereBanco1;
DELIMITER $$
CREATE TRIGGER insereBanco1 AFTER INSERT
ON banco1.clientes FOR EACH ROW

BEGIN
    /* insercao */
    BEGIN
            INSERT INTO banco2.Clientes (id, nome, dat_nascimento, cpf, rg, endereco, numero, bairro, cidade, estado, cep)
            SELECT id, nome, dat_nascimento, cpf, rg, endereco, numero, bairro, cidade, estado, cep FROM banco1.Clientes WHERE
            id NOT IN (SELECT id FROM banco2.Clientes);
    END;
END;

UPDATE

DROP TRIGGER alteraBanco1;
DELIMITER $$
CREATE TRIGGER alteraBanco1 AFTER UPDATE
ON banco1.clientes FOR EACH ROW

BEGIN
     /* alteracao */
    BEGIN
        UPDATE banco1.Clientes, banco2.Clientes SET banco2.Clientes.nome = banco1.Clientes.nome, 
        banco2.Clientes.dat_nascimento = banco1.Clientes.dat_nascimento, banco2.Clientes.cpf = banco1.Clientes.cpf,
        banco2.Clientes.rg = banco1.Clientes.rg, banco2.Clientes.endereco = banco1.Clientes.endereco, banco2.Clientes.numero = banco1.Clientes.numero, 
        banco2.Clientes.bairro = banco1.Clientes.bairro, banco2.Clientes.cidade = banco1.Clientes.cidade, banco2.Clientes.estado = banco1.Clientes.estado, banco2.Clientes.cep = banco1.Clientes.cep 
        WHERE banco1.Clientes.id = banco2.Clientes.id;
    END;
END;
$$  

Answer 1

You cannot change a table while the Trigger INSERT is firing. The INSERT can do some blocking that can result in a conflict. In addition, updating the table from a trigger would trigger it to fire again in an infinite recursive loop. Both reasons are why the MySQL prevents you from doing this.

However, depending on what you are trying to achieve, you can access the new values using NEW.fieldname or even the old values - if you are doing an UPDATE - with OLD.

If you had a named line full_brand_namee wish to use the first two letters as an abbreviated name in the field, small_namepoderia use:

CREATE TRIGGER `capital` BEFORE INSERT ON `brandnames`
FOR EACH ROW BEGIN
  SET NEW.short_name = CONCAT(UCASE(LEFT(NEW.full_name,1)) , LCASE(SUBSTRING(NEW.full_name,2)))
END

Reply translated from this post: https://stackoverflow.com/questions/15300673/mysql-error-cant-update-table-in-stored-function-trigger-because-it-is-already