oracle trigger ORA-04091

Question

I have craeted a trigger that watches one table. When the user changes the candidate_selected column to a Y I want oracle to lift columns from that table and insert them into a lookup table that keep track of candidates that are currently active i.e ='Y'. When I try to update the column I get error ORA-04091. The triggers does not change the table on which it is built and I'm stuck as to why I get this error. Any help would be greatly appreciated - I have been to a few sites and can't see the fix, think I've been staring at it for too long :)

CREATE OR REPLACE TRIGGER DEMO_SCHEMA.TRG_CANDIDATE_SELECTED
AFTER UPDATE OF CANDIDATE_SELECTED ON DEMO_SCHEMA.TBL_CANDIDATES FOR EACH ROW
BEGIN

    IF :NEW.CANDIDATE_SELECTED = 'Y' THEN

        INSERT INTO DEMO_SCHEMA.TBL_CANDIDATES_LKP 
            (UPDATED_DT, GROUP_ID, CAND_ID
            ,STAGE, STEP, EQUIPMENT, ORDER_REQ
            ,INCLUDED_IN_STUDY)
        SELECT SYSDATE, GROUP_ID, CAND_ID, STAGE
                    ,STEP, TRIM(REGEXP_SUBSTR(EQUIPMENT, '[^,]+', 1, LEVEL)) EQUIPMENT
                    ,(CASE WHEN UPPER(ORDER_REQ) = 'FALSE' THEN 1
                                 ELSE ROW_NUMBER() OVER (PARTITION BY GROUP_ID, CAND_ID, STAGE, STEP ORDER BY CAND_ID)
                        END)  ORDER_REQ, INCLUDED_IN_STUDY
        FROM (
            SELECT GROUP_ID, CAND_ID
                       ,STAGE, STEP, EQUIPMENT, EQUIPMENT EQUIP
                       ,ORDER_REQ, INCLUDED_IN_STUDY
            FROM DEMO_SCHEMA.TBL_CANDIDATES
            WHERE GROUP_ID = :NEW.GROUP_ID
            AND LOT_ID = :NEW.CAND_ID
            AND STAGE = :NEW.STAGE
            AND STEP = :NEW.STEP
            )    
        CONNECT BY LEVEL <= REGEXP_COUNT(EQUIP,', ')+1;

    ElSIF :NEW.CANDIDATE_SELECTED = 'N' THEN

        UPDATE DEMO_SCHEMA.TBL_CANDIDATE_LKP
        SET INCLUDED_IN_STUDY = :NEW.CANDIDATE_SELECTED
        WHERE GROUP_ID = :NEW.GROUP_ID
        AND CAND_ID = :NEW.CAND_ID
        AND STAGE = :NEW.STAGE
        AND STEP = :NEW.STEP;

    END IF;

END TRG_CANDIDATE_SELECTED;
/

There may be a syntax error in there but that is because i had to change a few things before I posted. This code does compile in Oracle. Cheers.

And what error message does "ORA-04091" belong to? Please supply the full error message alongside the error number; we're not all walking encyclopaedias of Oracle error numbers and their corresponding error messages! *{:-) — Boneist
A row-level trigger on tbl_candidates is not generally allowed to query tbl_candidates. Not knowing your data model, do you really need to query tbl_candidates or can you just use data that is already in the :new pseudo-record? — Justin Cave
@JustinCave but the tbl_candidates in the insert statement is in a different schema to the tbl_candidates the trigger is on, assuming the OP hasn't made a typo in their example code. — Boneist
@Boneist - I am assuming that is part of the "I had to change a few things before I posted" comment. I'm guessing that they are the same table and one of the two schema references was modified. — Justin Cave
@JustinCave *{:-) I should know better than to trust what the question says! *{;-) — Boneist

Boneist Boneist · Accepted Answer · 2016-07-04T16:54:19

As suggested by Justin Cave, your issue is that you're selecting from the same table that your trigger belongs to.

If you need to keep it as an insert as select statement due to needing to have a row per item in the EQUIPMENT column, then you can just select the new values from dual, like so:

CREATE OR REPLACE TRIGGER DEMO_SCHEMA.TRG_CANDIDATE_SELECTED
AFTER UPDATE OF CANDIDATE_SELECTED ON DEMO_SCHEMA.TBL_CANDIDATES FOR EACH ROW
BEGIN

    IF :NEW.CANDIDATE_SELECTED = 'Y' THEN

        INSERT INTO DEMO_SCHEMA.TBL_CANDIDATES_LKP 
            (UPDATED_DT, GROUP_ID, CAND_ID
            ,STAGE, STEP, EQUIPMENT, ORDER_REQ
            ,INCLUDED_IN_STUDY)
        SELECT SYSDATE, GROUP_ID, CAND_ID, STAGE
                    ,STEP, TRIM(REGEXP_SUBSTR(EQUIPMENT, '[^,]+', 1, LEVEL)) EQUIPMENT
                    ,(CASE WHEN UPPER(ORDER_REQ) = 'FALSE' THEN 1
                                 ELSE ROW_NUMBER() OVER (PARTITION BY GROUP_ID, CAND_ID, STAGE, STEP ORDER BY CAND_ID)
                        END)  ORDER_REQ, INCLUDED_IN_STUDY
        FROM (
            SELECT :NEW.GROUP_ID group_id,
                   :NEW.CAND_ID cand_id,
                   :NEW.STAGE stage,
                   :NEW.STEP step,
                   :NEW.EQUIPMENT equipment,
                   :NEW.EQUIPMENT EQUIP,
                   :NEW.ORDER_REQ order_req,
                   :NEW.INCLUDED_IN_STUDY included_in_study
            FROM dual
            )    
        CONNECT BY LEVEL <= REGEXP_COUNT(EQUIP,', ')+1;

    ElSIF :NEW.CANDIDATE_SELECTED = 'N' THEN

        UPDATE DEMO_SCHEMA.TBL_CANDIDATE_LKP
        SET INCLUDED_IN_STUDY = :NEW.CANDIDATE_SELECTED
        WHERE GROUP_ID = :NEW.GROUP_ID
        AND CAND_ID = :NEW.CAND_ID
        AND STAGE = :NEW.STAGE
        AND STEP = :NEW.STEP;

    END IF;

END TRG_CANDIDATE_SELECTED;
/

oracle trigger ORA-04091

1 Answers