Good day! I had to run into Oracle, but I’m a novice developer and ask strictly not to judge my code. When creating a document in the FORMS table, the following trigger is triggered:

CREATE OR REPLACE TRIGGER "REA"."AUTOCREATE_TRIGGER" BEFORE INSERT ON REA.FORMS FOR each ROW WHEN (NEW.LIST_OF_FORMS_ID=59 OR NEW.LIST_OF_FORMS_ID=60) BEGIN IF :NEW.LIST_OF_FORMS_ID=59 THEN AUTOCREATE.AUTOCREATE_PR340_FR73_S1_T1(:NEW.POWER_FACILITIES_VID, :NEW.ID); END

As you can see, the trigger calls a procedure that creates subdocuments and adds entries to the PR340_FR73_S1_T1 table and all subdocuments have a FORMS_ID that is associated with the ID in the FORMS table

  PROCEDURE AUTOCREATE_PR340_FR73_S1_T1( POWERFACILITIES NUMBER, FORMSID NUMBER) AS BEGIN FOR ROW IN (SELECT VID FROM poe_steam_turbines WHERE POWER_FACILITIES_VID = POWERFACILITIES AND IS_ACTUAL = 1) LOOP INSERT INTO PR340_FR73_S1_T1 ( HAS_PROLONGATION ----ПРОДЛЕНИЕ ,HAS_REMARKING ---ПЕРЕМАРКИРОВКА ,HAS_REPLACEMENT --ЗАМЕНА ,HAS_REMOVAL --ВЫВОД ,POE_STEAM_TURBINES_VID ,FORMS_ID ) VALUES ( 0 ,0 ,0 ,0 ,ROW.VID ,FORMSID ); END LOOP; END AUTOCREATE_PR340_FR73_S1_T1;

And at the moment of insertion into the table PR340_FR73_S1_T1, the trigger is triggered.

  CREATE OR REPLACE TRIGGER "PR340_FR73_S1_T1_EVENT" BEFORE INSERT ON PR340_FR73_S1_T1 FOR EACH ROW DECLARE period NUMBER; BEGIN SELECT PERIOD_ID INTO period FROM FORMS WHERE Id = :NEW.FORMS_ID; -- в этом запросе происходит ошибка из за :NEW.FORMS_ID --period:=2016; IF :NEW.HAS_REMOVAL = 0 THEN FILL_EVENT.FILL_REMOVAL_ATR(:NEW.POE_STEAM_TURBINES_VID, period, :NEW.STOP_ID, :NEW.REMOVAL_LAST_YEAR); END IF; END;

If the period variable is hard-coded, then everything is fine, or if instead of: NEW.FORMS_ID to substitute the value from another existing record, then also the norms, I think that all the same a new record is still created and I cannot get values ​​from it, since all the triggers are before, but how then can I get this value from the new FORMS table entry, before adding the record to the database, changing the trigger in AFTER only caused a lot of errors. Thanks for attention...

  • one
    What a mistake arises - Mike
  • Caused by: javax.persistence.PersistenceException: ERROR executing DML bindLog [] error [ORA-04091: REA.FORMS table is changed, the trigger / function may not notice this \ n ORA-06512: on "REA.FILL_EVENT", line 18 \ n ORA-06512: at "REA.SCI_TURBINE_PAR_EVENT", line 10 \ n ORA-04088: error during execution of the trigger 'REA.PR340_FR75-S1_T1_EVENT' \ n ORA-06512: at "REA.AUTOCREATE", line 549 \ n ORA -06512: on "REA.AUTOCREATE_TRIGGER", line 3 \ n ORA-04088: error during the execution of the trigger 'REA.AUTOCREATE_TRIGGER' \ n] - Egor Podolyak
  • 2
    True, I didn’t understand why your error doesn’t occur if you have hard-code id forms, it should always occur. Unfortunately in Oracle, it is generally impossible to refer to the table on which the trigger is triggered from for each row triggers. Standard solution: create another table. In the AFTER INSERT ON FORMS FOR each ROW trigger, write two fields in this table :NEW.POWER_FACILITIES_VID, :NEW.ID You make another trigger on FORMS, but without for each row in it, read this table and call your procedure. Clear the table - Mike
  • 3
    You have a trigger PR340_FR75_S1_T1 triggered when a record creates a procedure, and a procedure creates it when a trigger calls it on FORMS. Those. at the moment when you are applying to FORMS, the change of this table is not completed yet (not all of the for each row triggers have ended). The decision in the post above will help you for sure - Mike
  • 2
    Do you write your code or debug a stranger? If the first, it is better to rewrite your logic from triggers to procedures, because in this form it is not supported by the word at all (which, in fact, demonstrates your question). If the second - then you are not lucky, but try to persuade colleagues to refactor. - Dmitriy

0