how to reset sequences for different sets of records

From: Wallyraju <wallyraju_at_gmail.com>
Date: Mon, 14 Sep 2009 12:39:01 -0700 (PDT)
Message-ID: <f1f52d9e-4f33-4a7d-8c9c-fa2f6e442ab2_at_m11g2000vbl.googlegroups.com>

Oracle version 10.2.0.7
Windows 2003 Server

All,

I have a table where I need to reset and increment a certain column based on the following example

table: activity (primary_key not shown)
columns
parent_id activity_type_id recno

-------------  ---------------------- --------
1                         1             1
1                         1             2
1                         1             3
1                         2             1
1                         2             2
1                         3             1
1                         3             2
2                         2             1
2                         2             2
2                         3             1
2                         4             2
2                         4             3
2                         4             4

Basically I have to reset the recno for every combination of parent_id and activity_type_id.

I wrote the following trigger to calculate the recno.

CREATE OR REPLACE TRIGGER activity_trg

   BEFORE INSERT
   ON activity
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
   v_recno INTEGER;
   v_count INTEGER;
   --PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN    IF :NEW.recno IS NULL OR :NEW.recno = 0    THEN

      SELECT COUNT (1)
        INTO v_count
        FROM activity
       WHERE parent_id = :NEW.parent_id AND activity_type_id

= :NEW.activity_type_id;

      IF v_count = 0
      THEN
         v_recno := 1;
      ELSE
         SELECT MAX (recno) + 1
           INTO v_recno
           FROM activity
           WHERE parent_id = :NEW.parent_id AND activity_type_id


= :NEW.activity_type_id;

      END IF;

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

The trigger works when I insert a single record at a time. It raises a mutating trigger error when I do a insert from a select.

If I uncomment the line for the Pragma autonomous transaction then the code works both when I insert single record or when I run a insert from a select. But if I insert five records from a select for example where the parent_id = 2 and the activity_id = 4, then all records get the recno = 5 since the autonomous transaction only sees the original records and always returns 4+1 = 5.

Any help to solve this issue would be appreciated.

Thanks in advance.

Wally Received on Mon Sep 14 2009 - 14:39:01 CDT