How to use new in SQL?

Asked

Viewed 129 times

1

My goal is: so insert an element into banco1.Clientes the same is inserted in banco2.Clientes via a Trigger. If this element is already in the bank, there should be an update. But I cannot use the NEW correctly. Code of the Trigger: 

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

BEGIN
    /* insercao */
    BEGIN
            IF (NEW.id NOT IN (SELECT id FROM banco2.Clientes)) THEN
            INSERT INTO banco2.Clientes VALUES (NEW.id, NEW.nome, NEW.dat_nascimento, NEW.cpf, NEW.rg, NEW.endereco, 
            NEW.numero, NEW.bairro, NEW.cidade, NEW.estado, NEW.cep);
            ELSE
                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 IF;
    END;
END;

$$

The mistake:

Error Code: 1146. Table 'banco1.NEW' doesn't exist
sql trigger
  • 1

    SQL is the language, and it seems that your problem is language specific. Mysql is the server itself.

  • Change the INSERT to something like this: INSERT INTO BANCO2.CLIENTS VALUES ( new.id, new.nome....) Remove the Constraint from the CLIENTS table for now, to see how the records are being entered. When everything is correct enable the Constraint.

  • Reginaldo, good afternoon! I made the appropriate changes, and now the error is 1442: "Error Code: 1442. Can’t update table 'clients' in stored Function/Trigger because it is already used by statement which Invoked this stored Function/Trigger."

  • I inserted the modified Trigger in the theme

1 answer

0

You are mixing banco1 and banco2 in the update. Although it is possible, it is much easier to read and implement using the variables of NEW available within Trigger’s scope.

Remove all references to banco1.Clientes update and replace them with the equivalent values of NEW.

Something like:


CREATE TRIGGER insereBanco1 AFTER INSERT
ON banco1.clientes FOR EACH ROW
BEGIN    
    IF (NEW.id NOT IN (SELECT id FROM banco2.Clientes)) THEN
      INSERT INTO banco2.Clientes VALUES (
        NEW.id, 
        NEW.nome, 
        NEW.dat_nascimento, 
        NEW.cpf, 
        NEW.rg, 
        NEW.endereco, 
        NEW.numero, 
        NEW.bairro, 
        NEW.cidade, 
        NEW.estado, 
        NEW.cep);
    ELSE
        UPDATE banco2.Clientes 
           SET banco2.Clientes.nome = NEW.nome, 
               banco2.Clientes.dat_nascimento = NEW.dat_nascimento, 
               banco2.Clientes.cpf = NEW.cpf,
               banco2.Clientes.rg = NEW.rg, 
               banco2.Clientes.endereco = NEW.endereco, 
               banco2.Clientes.numero = NEW.numero, 
               banco2.Clientes.bairro = NEW.bairro, 
               banco2.Clientes.cidade = NEW.cidade, 
               banco2.Clientes.estado = NEW.estado, 
               banco2.Clientes.cep = NEW.Clientes.cep 
        WHERE banco2.Clientes.id = NEW.id;
    END IF;
END;

Browser other questions tagged sql trigger

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