Hi,
I'm trying to map a c++ object model ( container pattern ) into
oracle table.
As you'll see in this code segment, I'm trying to delete from the
table in a trigger when those entry become invalid. At first, i was
deleting directly from the trigger but this gave me an 'table is
mutating' error, which is a good thing after you get trough the
frustration :-)
Then i though, well if i do it in a autonomous transaction, it
should do the trick.... Here's what i did.
- That's the table that is parent for every thing.
create table vectorroot(
id number(20,0) primary key
)
/
- vector2 table.
create table vector2(
id number(20,0) primary key references vectorroot on delete cascade,
v0 float,
v1 float
)
/
- Table containing vector sets.
create table vectorset(
id number(20,0) primary key references vectorroot on delete cascade,
vId1 number(20,0) references vector2,
vId2 number(20,0) references vector2
)
/
- Autonomous transaction to remove a vector2 entry.
create procedure deleteVector2(
in_id number
) is
pragma autonomous_transaction;
begin
delete from vectorroot where id = in_id;
end;
/
- Trigger that automaticcaly remove vector2 entry when vectorset
- gets deleted.
create trigger onDeleteVectorSet
after delete on vectorset for each row
begin
deleteVector2( :old.vId1 );
deleteVector2( :old.vId2 );
end;
/
- Register some vector2 and vectorset.
insert into vectorroot values( 1 );
insert into vectorroot values( 2 );
insert into vectorroot values( 3 );
insert into vectorroot values( 4 );
insert into vectorroot values( 5 );
insert into vectorroot values( 6 );
insert into vectorroot values( 7 );
insert into vectorroot values( 8 );
insert into vector2 values( 1, 0, 0 );
insert into vector2 values( 2, 0, 0 );
insert into vector2 values( 3, 0, 0 );
insert into vector2 values( 4, 0, 0 );
insert into vector2 values( 5, 0, 0 );
insert into vector2 values( 6, 0, 0 );
insert into vectorset values( 7, 1, 2 );
insert into vectorset values( 8, 3, 4 );
- delete a vectorset to test the results.
delete from vectorroot where id = 7;
doing so I get this error :
ORA-00060: deadlock detected while waiting for resource
ORA-04088: error during execution of trigger
So here's two question :
- Does anyone know a way around that will enable me to delete the
value
from the vector2 table?
- I though that specifying 'pragma autonomous_transaction' into a
procedure
would put the statement into the queue and that they would be
executed in
another transaction, hence not getting a deadlock since the lock
should be
released by my trigger when the statement gets executed. Obviously,
i'm
missing something here... any help on that one ?
Thanks in advance
Hugues Landry ... a poor programmer that's trying to understand the
relational way of life.
PS : i tried to post this yesterday but my message got lost :-( If two
of those
appear in the group i'm really sorry about it !
Received on Mon Jan 21 2002 - 11:19:14 CST
