Update table with a trigger - How to solve mutation error-CodePudding

I'm trying to create a trigger for updating some columns of a record after creating or updating the record.

There my code:

CREATE OR REPLACE TRIGGER "TRIGGER_UPDATE_CONTRACTOR_LOT"
AFTER INSERT OR UPDATE ON "CONTRACTOR_LOT"
FOR EACH ROW
DECLARE
    CONTRACT_LOT_LABEL VARCHAR2(255 BYTE);
    CONTRACTOR_LABEL VARCHAR2(200 BYTE);
BEGIN

       SELECT LABEL INTO CONTRACT_LOT_LABEL FROM LOT_T WHERE ID = :NEW.LOT_ID;
       SELECT CONTRACTOR INTO CONTRACTOR_LABEL FROM CONTRACTOR_T WHERE ID = :NEW.CONTRACTOR_ID;

       UPDATE CONTRACTOR_LOT
        SET LABEL = CONTRACT_LOT_LABEL  || ':' || CONTRACTOR_LABEL,
            FRAMEWORK_CONTRACT_NUMBER_LABEL = :NEW.ORDER || ':' || CONTRACT_LOT_LABEL  || :NEW.FRAMEWORK_CONTRACT_NUMBER
        WHERE ID = :NEW.ID;  
END;

I get an error ORA-04091 (mutation) I tried to add PRAGMA AUTONOMOUS_TRANSACTION; and I get the error ORA-00060 (deadlock detected while waiting for resource) So I add COMMIT; after the update, but it's still the same issue.

Could you help me please with that?

CodePudding user response：

You can't perform DML on the parent table within a trigger, and there's really rarely any reason to do so. Try this, with a BEFORE trigger and just modifying the NEW values of the existing INSERT or UPDATE that is firing the trigger:

CREATE OR REPLACE TRIGGER TRIGGER_UPDATE_CONTRACTOR_LOT
BEFORE INSERT OR UPDATE ON CONTRACTOR_LOT
FOR EACH ROW
DECLARE
    CONTRACT_LOT_LABEL VARCHAR2(255 BYTE);
    CONTRACTOR_LABEL VARCHAR2(200 BYTE);
BEGIN
    SELECT LABEL INTO CONTRACT_LOT_LABEL FROM LOT_T WHERE ID = :NEW.LOT_ID;
    SELECT CONTRACTOR INTO CONTRACTOR_LABEL FROM CONTRACTOR_T WHERE ID = :NEW.CONTRACTOR_ID;

    NEW.CONTRACTOR_LOT := CONTRACT_LOT_LABEL  || ':' || CONTRACTOR_LABEL;
    NEW.FRAMEWORK_CONTRACT_NUMBER_LABEL := :NEW.ORDER || ':' || CONTRACT_LOT_LABEL  || :NEW.FRAMEWORK_CONTRACT_NUMBER;
END;

CodePudding user response：

Your trigger is catching changes and inserts on this table and then update on the same table. It seems to be vicious circle. Because update inside the trigger also will want to initiate this trigger