Mutating trigger on delete

[kjnorman]

Hello,

I have a problem with a mutating trigger. I have table that contains sort keys, and when a row is deleted, I want the sort keys to be resorted so that there are no gaps. Ie, if I have 100 rows, sorted 1 to 100, then if row 50 is deleted, then 51 to 100 to be renumbered 50 to 99.

So far this have been handled by a procedure, but this does not protect the data should someone type "delete from table where id = ??", so I have been trying to do this with a trigger, but so far with little success.

The trigger on the table is:

create or replace
trigger scpm.mplant_aft_del
after delete
on scpm.master_plant
for each row
Begin
update scpm.master_plant
set sort_key = sort_key -1
where sort_key > ld.sort_key;
end;

There is also a before insert and update trigger :

create or replace
trigger scpm.mplant_b4_ins_upd
before
insert or
update of plant_desc, plant_long, plant_alias, iso_num, plant_type_id, plant_status
on scpm.master_plant
for each row
Begin
if inserting then
select mplant_id_seq.nextval into :new.plant_id from dual;
if :new.sort_key < 1 then
/* find maximum (bottom of range) sort position */
select nvl(max(sort_key),0)+1 into :new.sort_key from scpm.master_plant ;
else
/* reposition all higher (lower down the list) sort keys */
update scpm.master_plant
set sort_key = sort_key + 1
where sort_key >= :new.sort_key ;
end if;
:new.updt_user := user;
:new.updt_time := sysdate;
elsif updating then
/* We are updating */
:new.updt_user := user;
:new.updt_time := sysdate;
end if;
end;

The table in question is:

create table scpm.master_plant
( plant_id number(4) not null
, plant_desc varchar2(20) not null
, plant_long varchar2(100) not null
, plant_alias varchar2(20)
, iso_num number(3) not null
references scpm.iso_country (iso_num)
on delete cascade
, plant_type_id number(4) not null
references scpm.master_plant_type (plant_type_id)
on delete cascade
, plant_status varchar2(10) not null
, sort_key number(4) not null
, updt_user varchar2(10) not null
, updt_time date not null
, constraint mplant_pk primary key (plant_id)
using index pctfree 5 initrans 4
tablespace indx storage ( initial 32768 next 16384 pctincrease 0)
, constraint mplant_desc_u unique (plant_desc)
using index pctfree 5 initrans 4
tablespace indx storage ( initial 32768 next 16384 pctincrease 0)
, constraint mplant_long_u unique (plant_long)
using index pctfree 5 initrans 4
tablespace indx storage ( initial 32768 next 16384 pctincrease 0)
, constraint mplant_sort_u unique (sort_key)
using index pctfree 5 initrans 4
tablespace indx storage ( initial 32768 next 16384 pctincrease 0)
)
pctfree 5 pctused 60 initrans 2 maxtrans 255
tablespace usr storage ( initial 32768 next 16384 pctincrease 0)
/

Any ideas?

Thanks
Kerry

 

[sem]

You may read about resolving this (mutating) problem in multiple threads here, look for the one you like.
In general you should store the rows you're willing to update somewhere else (package variable or temporary table) and then perform real updates from statement level trigger (don't forget to set/reset some flag to avoid recursions).
Though I wonder why don't you create procedure to manage deletes also? I suppose it to be far more simplier. And I'd also like to let you know, that you may expect a lot of problems with locking, if you're working in multiuser environment and do need this procedure to work correctly.

Regards, Dima