Update table via Precedent/Rigger

Asked

Viewed 37 times

0

I have the following table structure:

    CREATE TABLE FORNECEDOR(
      ID INT PRIMARY KEY,
      CNPJ varchar(255),
      ID_FOUR INT
    );

   CREATE TABLE PRE_FORNECEDOR(
     ID INT PRIMARY KEY,
     CNPJ varchar(255),
     ID_FOUR INT
   );

I need to create Trigger/Procedure that after inserting a new record in the table FORNECEDOR, take that same new record and do an update in the field ID_FOUR. That one ID_FOUR is present in the table PRE_FORNECEDOR and I can track you through the countryside CNPJ, which is common between the two tables. This being said, I tried the implementation as follows:

CREATE FUNCTION PUBLIC.FNI_FORNECEDOR_HOMOLOGADO() RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS $$
BEGIN
    UPDATE PUBLIC.FORNECEDOR SET ID_FOUR = (
        SELECT PRE.ID_FOUR FROM PRE_FORNECEDOR PRE
        WHERE PRE.CNPJ = NEW.CNPJ
    ) WHERE ID = NEW.ID;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER TG_FORNECEDOR_HOMOLOGADO
    AFTER INSERT ON PUBLIC.FORNECEDOR
    FOR EACH ROW EXECUTE PROCEDURE FNI_FORNECEDOR_HOMOLOGADO();

But I get the following error message when executing the creation:

[42601] ERROR: conflicting or redundant options Posição: 264

I’ve done some procedures updating data from an x table with the update/Insert result from another y table, but I couldn’t solve in this case that I need to insert a record in table X and then update this same tuple. Test cases:

    INSERT INTO PRE_FORNECEDOR(ID, CNPJ, ID_FOUR) VALUES (1, '46972782000121', 55);
    --nesse momento a trigger deve ser acionada:
    INSERT INTO FORNECEDOR(ID, CNPJ) VALUES (1, '46972782000121');
postgresql

  • I think it would be better to control the steps remove the Rigger and put everything in a previous

  • @Ricardopunctual sincerely did not understand what you meant, but thank you for taking the time and reply.

  • i meant to remove Trigger and put all the steps in a single file, the Inserts and the update :)

  • @Ricardopunctual but I need Rigger, because she’s the trigger to run the trial. Anyway... the problem is not that but the compiler error.

  • and which is the wrong line?

1 answer

0

I discovered the problem, had declared the wrong variable, follows correct script:

CREATE OR REPLACE FUNCTION PUBLIC.FNI_FORNECEDOR_HOMOLOGADO() RETURNS TRIGGER
AS $test$
BEGIN
    IF EXISTS (SELECT PRE.ID_FOUR FROM PRE_FORNECEDOR PRE WHERE PRE.CNPJ = NEW.CNPJ) THEN
        UPDATE PUBLIC.FORNECEDOR SET ID_FOUR = (
            SELECT PRE.ID_FOUR FROM PRE_FORNECEDOR PRE
            WHERE PRE.CNPJ = NEW.CNPJ
        ) WHERE ID = NEW.ID;
    END IF;
    RETURN NULL;
END;
$test$ LANGUAGE PLPGSQL;

CREATE TRIGGER TG_FORNECEDOR_HOMOLOGADO
    AFTER INSERT ON PUBLIC.FORNECEDOR
    FOR EACH ROW EXECUTE PROCEDURE FNI_FORNECEDOR_HOMOLOGADO();

Browser other questions tagged postgresql

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