how to reset sequences for different sets of records

From: SolidSnake <wallyraju_at_gmail.com>
Date: Mon, 14 Sep 2009 12:21:01 -0700 (PDT)
Message-ID: <7c42b693-479f-4341-815a-60fa03628b10_at_f33g2000vbm.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:21:01 CDT

Original text of this message