Initialize sub sequence column values on insert. (Oracle)

Question

I would like my table to sequence its "order by" column based on it's TEMPLATE_ID. I would like this to happen on insert (via an insert trigger, probably). For example, if I run the following inserts, I should get the following table values.

INSERT INTO TEMPLATE_ATTRIBUTES (ID, TEMPLATE_ID) VALUES (1, 1)
INSERT INTO TEMPLATE_ATTRIBUTES (ID, TEMPLATE_ID) VALUES (2, 1)
INSERT INTO TEMPLATE_ATTRIBUTES (ID, TEMPLATE_ID) VALUES (3, 1)
INSERT INTO TEMPLATE_ATTRIBUTES (ID, TEMPLATE_ID) VALUES (4, 2)
INSERT INTO TEMPLATE_ATTRIBUTES (ID, TEMPLATE_ID) VALUES (5, 2)
INSERT INTO TEMPLATE_ATTRIBUTES (ID, TEMPLATE_ID) VALUES (6, 2)
INSERT INTO TEMPLATE_ATTRIBUTES (ID, TEMPLATE_ID) VALUES (7, 2)
INSERT INTO TEMPLATE_ATTRIBUTES (ID, TEMPLATE_ID) VALUES (8, 3)

ID TEMPLATE_ID ORDER_BY
 1           1        1
 2           1        2
 3           1        3
 4           2        1
 5           2        2
 6           2        3
 7           2        4
 8           3        1

I first tried to create this trigger, but it gives me an error when I insert.

create or replace
trigger TEMPLATE_ATTRIBUTES_AF_INS_TRIG
   after insert on TEMPLATE_ATTRIBUTES
   for each row
begin
    if :NEW.ORDER_BY is null then
       update TEMPLATE_ATTRIBUTES
       set ORDER_BY = (select coalesce(MAX(ta.ORDER_BY), 0) + 1 from TEMPLATE_ATTRIBUTES ta where ta.TEMPLATE_ID = :NEW.TEMPLATE_ID)
       where ID = :NEW.ID;
    end if;
end;

The error it gives me is: "table TEMPLATE_ATTRIBUTES is mutating, trigger/function may not see it"

So I need a different way to build this trigger. And I also need it to "thread safe" so that if these two inserts occur on different sessions at the same time, then the resulting records will still get different "ORDER_BY" values:

INSERT INTO TEMPLATE_ATTRIBUTES (ID, TEMPLATE_ID) VALUES (1, 1)
INSERT INTO TEMPLATE_ATTRIBUTES (ID, TEMPLATE_ID) VALUES (2, 1)

Edit:

I tried the common work around for the "table is mutating, trigger/function may not see it" and the work around "worked" but it was not "thread safe." I tried to add locking but it gave me another error on insert

create or replace package state_pkg
as 
  type ridArray is table of rowid index by binary_integer; 
  newRows ridArray;
  empty   ridArray; 
end;

create or replace trigger TEMPLATE_ATTRIBUTES_ORDER_BY_TB4 
before insert on TEMPLATE_ATTRIBUTES
begin
  state_pkg.newRows := state_pkg.empty; 
end;

create or replace trigger TEMPLATE_ATTRIBUTES_ORDER_BY_TAF1
after insert on TEMPLATE_ATTRIBUTES for each row 
begin
  if :NEW.ORDER_BY is null then
    state_pkg.newRows( state_pkg.newRows.count+1 ) := :new.rowid;
  end if;
end;

create or replace trigger TEMPLATE_ATTRIBUTES_ORDER_BY_TAF2
after insert on TEMPLATE_ATTRIBUTES
declare
  v_request     number;
  v_lockhandle varchar2(200);
begin
  dbms_lock.allocate_unique('TEMPLATE_ATTRIBUTES_ORDER_BY_lock', v_lockhandle);
  while v_request <> 0 loop
    v_request:= dbms_lock.request(v_lockhandle, dbms_lock.x_mode);
  end loop;
  begin
    for i in 1 .. state_pkg.newRows.count loop
      update TEMPLATE_ATTRIBUTES
      set ORDER_BY = (select coalesce(MAX(q.ORDER_BY), 0) + 1 from TEMPLATE_ATTRIBUTES q where q.TEMPLATE_ID = (select q2.TEMPLATE_ID from TEMPLATE_ATTRIBUTES q2 where q2.rowid = state_pkg.newRows(i)))
      where rowid = state_pkg.newRows(i);
    end loop;
    v_request:= dbms_lock.release(v_lockhandle);
  EXCEPTION WHEN OTHERS THEN 
    v_request:= dbms_lock.release(v_lockhandle);
    raise;
  end;
end;

This gives me:

ORA-04092: cannot COMMIT in a trigger ORA-06512: at "SYS.DBMS_LOCK", line 250 ORA-06512: at "TEMPLATE_ATTRIBUTES_ORDER_BY_TAF2", line 5 ORA-04088: error during execution of trigger 'TEMPLATE_ATTRIBUTES_ORDER_BY_TAF2' ORA-06512

Edit 2: The ORDER_BY column must be an updateable column. ID actually uses a sequence and before insert trigger to set its values. I thought I was simplifying my question when I included it in the insert examples, but that was incorrect. ORDER_BY's initial value is not really related to ID, but rather to what order the records are inserted. But ID is sequenced so you can use that if it helps.

Answer 1

Updating the same table with an update trigger can cause a logical loop, hence the mutating table error. Try using a before insert trigger and set the max value + 1 of your order_by.

create or replace
trigger TEMPLATE_ATTRIBUTES_AF_INS_TRIG
   before insert on TEMPLATE_ATTRIBUTES
   for each row   
begin
    if :NEW.ORDER_BY is null then
      select max(order_by) + 1 into :new.order_by
      from template_attributes
      where template_id = :new.template_id;
    end if;
    :new.order_by := nvl(:new.order_by,1); -- For new templates
end;
/

Answer 2

The reason this won't work, it that you cannot select from or update a table in it's own row level triggers. What you could do, is write a table level trigger:

create or replace trigger TEMPLATE_ATTRIBUTES_AF_INS_TRIG
  after insert on TEMPLATE_ATTRIBUTES
begin
  update TEMPLATE_ATTRIBUTES a
  set 
    a.ORDER_BY = 
     (select 
       coalesce(MAX(ta.ORDER_BY), 0) 
     from 
       TEMPLATE_ATTRIBUTES ta 
     where 
        ta.TEMPLATE_ID = a.TEMPLATE_ID) + row_number
  where 
    a.ORDER_BY is null;
end;

I added row_number to prevent records getting the same ORDER_BY if you insert multiple records in one statement, but I'm not sure if it works that way and I cannot test it at the moment. I hope you get the general idea.