Tom,

I have a mutating trigger error, for which I know the cause, but I am looking for a fix.

--------------------------------------------------------------------------------
My setup:

select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
--------------------------------------------------------------------------------

I have tried to explain the situation as best as I could, please let me know if you need additional info.

I have a table where each set of data (combination of columns A and B) has a counter/sequence (column seq) associated with it, which starts at 1.

In step #1 the tables and data needed for the example are setup.
In step #2 the trigger that calcuates and resets the sequence is created.
In step #3 sample inserts are run to show that the trigger works in one scenario (single record inserts).
In step #4 has the insert statement (insert from select) that is raising the mutating trigger error.
In Step #5 the trigger is recreated as an autonomous transaction which allows the insert from select, although the sequences generated using this trigger are wrong.

Any and all help would be appreciated.

--------------------------------------------------------------------------------
-- 1. Initial setup.
CREATE TABLE TEST1
(
A VARCHAR2(1 BYTE),
B NUMBER,
SEQ NUMBER
);

Insert into TEST1(A, B, SEQ) Values ('A', 1, 1);
Insert into TEST1(A, B, SEQ) Values ('A', 1, 2);
Insert into TEST1(A, B, SEQ) Values ('A', 2, 1);
Insert into TEST1(A, B, SEQ) Values ('B', 1, 1);
Insert into TEST1(A, B, SEQ) Values ('B', 1, 2);
Insert into TEST1(A, B, SEQ) Values ('B', 1, 3);
Insert into TEST1(A, B, SEQ) Values ('C', 4, 1);
Insert into TEST1(A, B, SEQ) Values ('C', 4, 2);
Insert into TEST1(A, B, SEQ) Values ('C', 4, 3);
Insert into TEST1(A, B, SEQ) Values ('C', 5, 1);
Insert into TEST1(A, B, SEQ) Values ('C', 1, 1);
Insert into TEST1(A, B, SEQ) Values ('D', 2, 1);
Insert into TEST1(A, B, SEQ) Values ('F', 3, 1);
Insert into TEST1(A, B, SEQ) Values ('F', 3, 2);
Insert into TEST1(A, B, SEQ) Values ('F', 4, 1);
COMMIT;

CREATE TABLE TEST2
(
A VARCHAR2(1 BYTE),
B NUMBER
);

Insert into TEST2 (A, B) Values ('A', 1);
Insert into TEST2 (A, B) Values ('A', 1);
Insert into TEST2 (A, B) Values ('B', 2);
Insert into TEST2 (A, B) Values ('B', 2);
Insert into TEST2 (A, B) Values ('D', 5);
Insert into TEST2 (A, B) Values ('D', 5);
COMMIT;

select * from test1 order by a,b,seq;
select * from test2 order by a,b;

--------------------------------------------------------------------------------
-- 2. trigger to calculate and reset sequence

CREATE OR REPLACE TRIGGER test1_trig
BEFORE INSERT
ON TEST1 FOR EACH ROW
DECLARE
v_recno INTEGER;
v_count INTEGER;
--PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF :NEW.seq IS NULL OR :NEW.seq = 0
THEN
SELECT COUNT (1)
INTO v_count
FROM test1
WHERE a = :NEW.a AND b = :NEW.b;

IF v_count = 0
THEN
v_recno := 1;
ELSE
SELECT MAX (seq) + 1
INTO v_recno
FROM test1
WHERE a = :NEW.a AND b = :NEW.b;
END IF;

:NEW.seq := v_recno;
END IF;
END;
/
--------------------------------------------------------------------------------
-- 3. Sample insert where the trigger works.

SET DEFINE OFF;
Insert into TEST1(A, B) Values ('A', 2);
Insert into TEST1(A, B) Values ('A', 2);
Insert into TEST1(A, B) Values ('A', 2);
Insert into TEST1(A, B) Values ('D', 5);
Insert into TEST1(A, B) Values ('D', 5);
COMMIT;

select * from test1 order by a,b,seq;
--------------------------------------------------------------------------------
-- 4. Insert statement that raises the mutating trigger error.

insert into test1 (a,b) select a,b from test2;

Error at line 1
ORA-04091: table TEST1 is mutating, trigger/function may not see it
ORA-06512: at "TEST1_TRIG", line 8
ORA-04088: error during execution of trigger 'TEST1_TRIG'
--------------------------------------------------------------------------------
--5. Changing the trigger to an autonomous transaction
CREATE OR REPLACE TRIGGER test1_trig
BEFORE INSERT
ON TEST1 FOR EACH ROW
DECLARE
v_recno INTEGER;
v_count INTEGER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF :NEW.seq IS NULL OR :NEW.seq = 0
THEN
SELECT COUNT (1)
INTO v_count
FROM test1
WHERE a = :NEW.a AND b = :NEW.b;

IF v_count = 0
THEN
v_recno := 1;
ELSE
SELECT MAX (seq) + 1
INTO v_recno
FROM test1
WHERE a = :NEW.a AND b = :NEW.b;
END IF;

:NEW.seq := v_recno;
END IF;
END;
/

-- running the same insert from select statement
insert into test1 (a,b) select a,b from test2;
commit;

-- selecting the data shows that the sequence is repeated
select * from test1 order by a,b,seq;
--------------------------------------------------------------------------------

Thank you again for your help.

Wally

BEGIN
  IF :NEW.seq IS NULL OR :NEW.seq = 0
  THEN
    SELECT COUNT (1)
    INTO v_count
    FROM test1
    WHERE a = :NEW.a AND b = :NEW.b;

    IF v_count = 0
    THEN
      v_recno := 1;
    ELSE
      SELECT MAX (seq) + 1
      INTO v_recno
      FROM test1
      WHERE a = :NEW.a AND b = :NEW.b;
    END IF;

    :NEW.seq := v_recno;
  END IF;
END;
/ 

ops$tkyte%ORA10GR2> create or replace view v
  2  as
  3  select a, b, seq, row_number() over (partition by a,b order by seq) rn
  4    from t
  5  /

View created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from v;

A          B        SEQ         RN
- ---------- ---------- ----------
A          1      10321          1
A          1      10744          2
A          2      11167          1
B          1      11590          1
B          1      12013          2
B          1      12436          3
C          1      14551          1
C          4      12859          1
C          4      13282          2
C          4      13705          3
C          5      14128          1
D          2      14974          1
F          3      15397          1
F          3      15820          2
F          4      16243          1

15 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( 'A', 1, s.nextval );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set autotrace on explain
ops$tkyte%ORA10GR2> select * from v where a = 'A' and b = 1;

A          B        SEQ         RN
- ---------- ---------- ----------
A          1      10321          1
A          1      10744          2
A          1      16666          3


Execution Plan
----------------------------------------------------------
Plan hash value: 2858554846

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |    10 |   410 |    10  (10)| 00:00:01 |
|   1 |  VIEW                         | V     |    10 |   410 |    10  (10)| 00:00:01 |
|   2 |   WINDOW SORT                 |       |    10 |   430 |    10  (10)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T     |    10 |   430 |     9   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T_IDX |  1000 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"='A' AND "B"=1)

ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2> delete from t where seq = (select min(seq) from t where a = 'A' and b = 1 );

1 row deleted.

ops$tkyte%ORA10GR2> select * from v where a = 'A' and b = 1;

A          B        SEQ         RN
- ---------- ---------- ----------
A          1      10744          1
A          1      16666          2