deadlock with trigger and foreign key
697857Apr 23 2009 — edited May 28 2009Hi,
I have created this two tables:
create table orig (
id Number(10,0) primary key
);
create table replica (
id Number(10,0)
);
alter table replica add constraint replica_fkey foreign key (id) references orig (id );
What i want is when inserting a value in table orig, the inserted value in "orig" table must be replicated to table "replica". So i used a trigger and a procedure:
CREATE or replace Procedure replicate ( id IN NUMBER ) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert into replica values ( id );
commit;
END;
CREATE or replace TRIGGER trigger_replicate AFTER INSERT OR UPDATE ON orig FOR EACH ROW
BEGIN
BEGIN
replicate (:NEW.id);
END;
END;
With this approach i get the ORA-00060 "deadlock detected while waiting for resource" error, (because of the foreign key i supose).
Is there another way to accomplish this work without removing the foreign key?
thanks in advance,
Sérgio Lopes