Re: deadlock in trigger.

Yes there is a solution,

but not easy (and not for my because my english is poor)

create package with a Collection
TYPE T_NUMBER IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; T_COD T_NUMBER;
in the the trigger before delete on each row, add your key in the collection

T_COD(T_COD.COUNT + 1) = COD; and in the trigger AFTER STATEMENT DELETE

FOR I in1..T_COD.COUNT
LOOP
  .... T_COD(i) ...
END LOOP; Very hazardeous but usefull!

Akshell

"Hugues Landry" <hugues_landry_at_videotron.ca> a écrit dans le message de news: 3XI28.863$%p2.84232_at_weber.videotron.net...
> Hi i got this deadlock problem while trying to map an object model into
> oracle table. Oracle
> tell to me that two request are waiting for the same resources and he is
> correct Here's what i
> do.
>
> --------------------------------------------------------------
> create table vectorroot(
> vid number(20,0) primary key,
> vName varchar2(50)
> )
> /
>
> create table vector2(
> vid number(20,0) primary key references verctorroot on delete cascade,
> v0 float,
> v1 float,
> v2 float
> )
> /
>
> create table vectormap(
> vid number(20,0) primary key references vectorroot on delete cascade,
> vec1 number(20,0) references vector2,
> vec2 number(20,0) references vector2
> )
> /
>
> create procedure deleteVector2(
> in_vid number(20,0)
> ) is
> pragma autonomous_transaction;
> begin
> delete from vector2 where vid = in_vid;
> end;
> /
>
> create trigger onDeleteVectorMap
> on delete on vectormap for each row
> begin
> call deleteVector2( :old.vec1 );
> call deleteVector2( :old.vec2 );
> end;
> /
>
> -------------------------------------------------------------------------
>
> At first, I was deleting directly from the trigger. As you can imagine,
this
> didn't work because the table was mutating. I tought the autonomous
> transaction would take care of this since it's done after. But finally it
> get
> me the deadlock ( This is a valid sql-plus script by the way ).
>
> I really need to have the vectorroot table ( this is a mapping for the c++
> container patern ). Does anyone know a workaround that would enable
> me to delete from the vector2 table within a trigger without blowing
> everything
> up :-)
>
> Thanks in Advance.
>
> Hugues Landry. ( A poor c++ programmer that try to understanding the
> relational way of life ).
>
>
>
Received on Thu Jan 24 2002 - 15:30:48 CST