1
votes

In order to solve the mutating table error, I followed http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS2005, but still it isn't working. Can some help to identify where is the problem of the following trigger?

Thanks,

CREATE OR REPLACE TRIGGER trg_d_inq_2 FOR
UPDATE OF hu1_dimension_lvl1
ON d_inq_dimensions
COMPOUND TRIGGER
v_exists_d NUMBER;
v_exists_c NUMBER;

TYPE process_t IS TABLE OF d_inq_dimensions.dimension_value%TYPE;

process process_t;

TYPE process_pvoc_t IS TABLE OF NUMBER
    INDEX BY VARCHAR2(100 BYTE);

process_pvoc process_pvoc_t;

TYPE nprocessvoc_t IS TABLE OF NUMBER;

nprocessvoc nprocessvoc_t;
BEFORE EACH ROW
IS
BEGIN
    SELECT a.hu1_dimension_lvl1, COUNT(a.hu1_dimension_lvl1)
      BULK COLLECT INTO process, nprocessvoc
      FROM d_inq_dimensions a
     WHERE a.dimension_name = 'Processo'
    GROUP BY a.hu1_dimension_lvl1;

    FOR j IN 1 .. process.COUNT
    LOOP
        process_pvoc(process(j)) := nprocessvoc(j);
    END LOOP;
END
BEFORE EACH ROW;

AFTER EACH ROW
IS
BEGIN
    IF :new.hu1_dimension_lvl1 IS NOT NULL AND
       :new.dimension_name = 'Processo'
    THEN
        IF process_pvoc(:old.hu1_dimension_lvl1) IS NULL
        THEN
            DELETE external.c_parameters
             WHERE proj_id = 79 AND
                   ind_id = 53 AND
                   lvl_2 = :old.hu1_dimension_lvl1;
        END IF;

        SELECT COUNT(1)
          INTO v_exists_c
          FROM external.c_parameters a
         WHERE proj_id = 79 AND
               ind_id = 53 AND
               lvl_2 = :new.hu1_dimension_lvl1;

        IF v_exists_c = 0
        THEN
            INSERT INTO external.c_parameters
            /*and something more*/
        END IF;
    END IF;
END
AFTER EACH ROW;

END;
1
That is an awful lot of work to do in a trigger... this seems like it would be a lot better in a procedure... - Ben
Mutating table error is caused by SELECT ... FROM d_inq_dimensions inside a BEFORE EACH ROW section. A row level trigger cannot perform any DML commands on a table that owns that trigger. You can place this SELECT in BEFORE STATEMENT or AFTER STATEMENT sections, but not in BEFORE/AFTER EACH ROW sections. - krokodilko
A compound trigger doesn't in and of itself solve the mutating table error. It just brings all the various triggers (before row, after statement, etc.) together. - GriffeyDog
THEN INSERT INTO external.c_parameters /*and something more*/ END IF; Is there any :NEW used in this insert? If yes, then it will cause the problem. Is there any other trigger found on those tables to change in source table? - SriniV
Thank you for the explanation. It seems there are many problems in the trigger. First, the select inside before each row and the :NEW in /*something more*/ which I forgot to include in the question. - Pedro Pires

1 Answers

1
votes

Use after statement instead of BEFORE EACH ROW.