Re: how to reset sequences for different sets of records
Date: Tue, 15 Sep 2009 06:39:20 -0700 (PDT)
Message-ID: <cb0d7ec8-e04b-4528-ae3c-cfba3dc814cf_at_o13g2000vbl.googlegroups.com>
On Sep 14, 3:39 pm, Wallyraju <wallyr..._at_gmail.com> wrote:
> 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
Wally, Charles has posted a basic means of how to re-sequence the data in a query. I would like to ask why do you need to re-sequence the data like this? What business need is being accomplished?
That is if you only need to display the data with the posted sequence then it can be done via a query and it may be unnecessary to update the table data itself.
If you really need to update the table data I would think you have to take into account the Oracle read consistency model. Namely updaters do not block readers. Hence if multiple sessions run the logic you may end up with unique constraint violations or duplicate data depending on how the data is indexed because the second session reader will not be blocked by the first session update. You will need to resort to select for update logic. This can limit scalability of the application.
If you must maintain the data column values in relative row order I would suggest performing all updates via stored code. All inserts, updates, and deletes will be done via a stored procedure that uses select for update on the target rows. As long as multiple sessions normally go after different leading keys the application should scale. To ensure no direct DML gets done you can replace the table with a view and place instead of triggers on the view to use the procedure to perform the DML.
But if possible just use Charles's post as a guide and just make the change in the result set.
HTH -- Mark D Powell -- Received on Tue Sep 15 2009 - 08:39:20 CDT