0
I’m trying to create a trigger
to update my stock of products, when the status of my purchase is closed (CF = Closed Purchase).
However, it is sending me an error, and I could not identify what is causing this error to be generated. Does anyone have any hints ?
O erro é Erro(3,5): PL/SQL: SQL Statement ignored
Erro(3,70): PL/SQL: ORA-00904: "NEW"."IDCOMPRA": identificador inválido
Erro(6,7): PL/SQL: SQL Statement ignored
Erro(6,99): PL/SQL: ORA-00904: "NEW"."IDPRODUTO": identificador inválido
What I did:
CREATE TRIGGER ATUALIZA_ESTOQUE BEFORE INSERT ON ITEMCOMPRA FOR EACH ROW
DECLARE SITUACAOCOMPRA CHAR(2);
BEGIN
SELECT SITUACAOCOMPRA INTO SITUACAOCOMPRA FROM COMPRA WHERE ID = NEW.IDCOMPRA;
IF(SITUACAOCOMPRA = 'CF') THEN
--BEGIN
UPDATE PRODUTO SET QUANTIDADEPRODUTO = QUANTIDADEPRODUTO + NEW.QUANTIDADEPRODUTO WHERE ID = NEW.IDPRODUTO;
--END;
END IF;
END;
it returns me Error(6,67): PLS-00049: 'NEW.QUANTITYINVALID' link variable
– Gustavo Santos
Strange, the Rigger is apparently correct. tries to reference new and old as my change. If not, try to store this value in a variable above and change the update to use it in the set.
– Damon Dudek
no, it persists
– Gustavo Santos
I edited there, because FOR was in the line of create, but he must go on FOR EACH ROW getting REFERENCING NEW AS NEW AS OLD FOR EACH ROW and erasing from the top line
– Mateus
I had already fixed it myself, he persisted anyway
– Gustavo Santos
The more I believe that Trigger is correct, it must be problem with the table
– Mateus
I solved, was some details in ID!
– Gustavo Santos
@Matthew this type of editing you did is discouraged, never change codes or details that change the direction of the author’s post, always leave a comment instead.
– user28595
yes, Trigger is correct, check that in the table the field is exactly with that name QUANTITYPRODUCT. to make a test, remove this variable that is signaling the error.
– Damon Dudek