7 Replies Latest reply: Oct 22, 2012 12:48 PM by rp0428 RSS

    Initialize sub sequence column values on insert?

    969929
      I asked this on stack overflow, but it was recommended that I also ask here.

      http://stackoverflow.com/questions/12982875/initialize-sub-sequence-column-values-on-insert-oracle

      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.
        • 1. Re: Initialize sub sequence column values on insert?
          AlbertoFaenza
          Check here below:
          create table TEMPLATE_ATTRIBUTES
          ( ID           INTEGER
          , TEMPLATE_ID  INTEGER
          , ORDER_BY     INTEGER
          );
          
          CREATE OR REPLACE TRIGGER templ_attr_bf_ins_trg
             BEFORE INSERT
             ON template_attributes
             FOR EACH ROW
          BEGIN
             IF :new.order_by IS NULL
             THEN
                SELECT NVL (MAX (ta.order_by), 0) + 1
                  INTO :new.order_by
                  FROM template_attributes ta
                 WHERE ta.template_id = :new.template_id;
             END IF;
          END;
          
          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);
          
          SELECT * FROM TEMPLATE_ATTRIBUTES;
          
          Output:
                  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
          {code}
          
          Let me also comment that I don't like this solution. It might conflict with multiuser access.
          If you just need the column to order the table you can use a sequence and then order you table by template_id, order_by (generated by a sequence).
          
          In this way you will not have a problem with multiuser access. Do you care that order_by column is not starting from 1 for each template_id and it has "holes" in the sequence for that template_id?
          
          Regards.
          Al
          
          Edited by: Alberto Faenza on Oct 22, 2012 5:11 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
          • 2. Re: Initialize sub sequence column values on insert?
            Solomon Yakobson
            Alberto Faenza wrote:
            Check here below:
            The only reason it worked is INSERT VALUES. As soon as data is coming from a subquery:
            SQL> INSERT INTO TEMPLATE_ATTRIBUTES (ID, TEMPLATE_ID)
              2  SELECT 1,1 FROM DUAL
              3  /
            INSERT INTO TEMPLATE_ATTRIBUTES (ID, TEMPLATE_ID)
                        *
            ERROR at line 1:
            ORA-04091: table SCOTT.TEMPLATE_ATTRIBUTES is mutating, trigger/function may not see it
            ORA-06512: at "SCOTT.TEMPL_ATTR_BF_INS_TRG", line 4
            ORA-04088: error during execution of trigger 'SCOTT.TEMPL_ATTR_BF_INS_TRG'
            
            
            SQL> 
            OP's whole design is flawed.

            SY.
            • 3. Re: Initialize sub sequence column values on insert?
              AlbertoFaenza
              Hi Solomon,

              totally right. In fact I tend to avoid this kind of trigger. I have also edited my post adding to prefer the use of a sequence.

              I was about to update my post with the example using a sequence:
              CREATE TABLE template_attributes
              (
                 id             INTEGER
               , template_id    INTEGER
               , order_by       INTEGER
              );
              
              CREATE SEQUENCE temp_attr_seq
                START WITH 1
                MAXVALUE 999999999999999999999999999
                MINVALUE 1
                NOCYCLE
                CACHE 20
                NOORDER;
              
              CREATE OR REPLACE TRIGGER templ_attr_bf_ins_trg
                 BEFORE INSERT
                 ON template_attributes
                 FOR EACH ROW
              BEGIN
                 SELECT temp_attr_seq.NEXTVAL INTO :new.order_by FROM DUAL;
              END;
              
              INSERT INTO TEMPLATE_ATTRIBUTES (ID, TEMPLATE_ID)
                SELECT 1,1 FROM DUAL;
              
              INSERT INTO TEMPLATE_ATTRIBUTES (ID, TEMPLATE_ID)
                SELECT 2,1 FROM DUAL;  
              
              
              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          4
                       5           2          5
                       6           2          6
                       7           2          7
                       8           3          8
              
              {code}
              
              I don't know if the user has some restriction or specific use of the order_by column.
              
              Regards.
              Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
              • 4. Re: Initialize sub sequence column values on insert?
                AlbertoFaenza
                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.
                Reading this I do not understand.

                Is ID already created by a sequence? So it is ordered according to the insert sequence?

                Can't you simply have your records ordered by
                  SELECT *
                    FROM template_attributes
                ORDER BY template_id, id;
                {code}
                
                Please clarify.
                
                You can also create the same column value with analytics:
                
                {code:sql}
                WITH ordered_data AS
                (
                  SELECT id, template_id, ROW_NUMBER() OVER (PARTITION BY template_id ORDER BY id) AS order_by
                )
                SELECT * 
                  FROM ordered_data;
                
                Output:
                        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
                {code}
                
                Regards.
                Al
                
                Edited by: Alberto Faenza on Oct 22, 2012 5:52 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                • 5. Re: Initialize sub sequence column values on insert?
                  969929
                  Al,

                  ID is already created by a sequence. But we only want to initialize the order_by values for the order that the records are inserted. We want the order_by column to be updateable so that records could be re-ordered with update statements.

                  I added those ID notes because a user on stack overflow wanted to know if the initial order_by value was based on ID or based on the order the records were inserted. The ID is actually sequenced, but when I wrote the question I wrote ID values in my insert statements which just ended up confusing him. He's deleted his answer so you cannot see it.


                  temp_attr_seq may be the closest we can get to our desired solution.


                  Edit:
                  This is actually closer to the true insert statements that we run:
                  INSERT INTO TEMPLATE_ATTRIBUTES (TEMPLATE_ID, ATTRIBUTE_ID) VALUES (1,123)

                  ID gets is value from a sequence with a before insert trigger. It was a mistake for me to include ID in my question. ID is really unrelated to order_id.

                  Also, TEMPLATE_ATTRIBUTES is a many to many join table. This is also unrelated to the question, but I want to demo exactly what kind of insert I am executing.

                  Thanks,

                  -Joel

                  Edited by: 966926 on Oct 22, 2012 9:16 AM

                  Edited by: 966926 on Oct 22, 2012 9:18 AM
                  • 6. Re: Initialize sub sequence column values on insert?
                    AlbertoFaenza
                    Hi,

                    the use of a sequence can give you the insertion order if you cannot rely on id.

                    It's not clear what you want to achieve.

                    Definitely you cannot use a trigger selecting from the same table.

                    Regards.
                    Al
                    • 7. Re: Initialize sub sequence column values on insert?
                      rp0428
                      >
                      But we only want to initialize the order_by values for the order that the records are inserted. We want the order_by column to be updateable so that records could be re-ordered with update statements.
                      >
                      But again the question is WHY?

                      Rows in an Oracle table are like balls in a basket; they have no order. Regardless of what data values you put into one, or multiple, columns the rows have no order.

                      The ONLY way to assure the order of rows is to add an ORDER BY clause when you extract the rows. Since no one can see the data until you extract it what difference does it make if you store an order column like that?

                      Your use case is not like the use case for INVOICEs where the LINE_ITEM table needs to have a record representing the actual line item on an invoice. For invoices you can't renumber them later.

                      As Alberto already showed you can add the ordering when you extract the data; you don't need to store it. If you do store it then any DML to the table can invalidate it anyway.