If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

I still need to be able to delete in both tables. Some times I delete records in table A (and needs corresponding data i table B to be deleted as well). And some times I delete records in table B (and needs corresponding data i table A to be deleted as well).

Maybe I don't understand your proposal correctly, but the foreign key with cascading deletes seems to me to be working the same ways as a delete-trigger - and therefore only one of the ways.

I don't understand you. Why do you want to delete the parent (two-way delete as mentioned in the initial post) if you are planning to insert a new "child" row afterwards anyway? Why do you need a two-way delete at all then?

You could declare the constraints as deferred then you could do the delete and insert inside a single transaction (because the constraint is evaluated when you commit the transaction)

I have two collateral tables, A and B. When I insert data into table A a trigger makes a new record i table B with the identical ID. Afterwards I some times want to delete records in table B - and then I want the related data in A to be deleted as well. Some times I want to delete data in table A and then I want the related data in B as well.

Why don't you combine the two bits of information into one table, they should not be separate anyway then you can have views to support the code. You delete through either view, the underlying row is gone.

Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice.

Y'know, if you're on version 10g or later, you could use dbms_utility.format_call_stack. It will give you the current call stack information, without having to design a STACK_PG on your own.

The code would look something like:

Code:

create trigger table1_bt
before delete
on table1
for each row
my_name varchar2(30) := 'TABLE1_BT';
call_stack VARCHAR2(2000);
begin
-- this looks to see if there is a second occurrence of this trigger
-- in the call stack. If it is, the round trip is occurring and you should
-- abort. Assumes that your TRIGGER names aren't similar enough that
-- an INSTR test would be positive for both TRIGGER names. For example,
-- the following 2 names are too similar: TABLE_BT and TABLE_BT2, while
-- these would be ok: TABLE_BT and TABLE2_BT. Using a stack would
-- get you around this potential mistake, but, this stack already exists for
-- you to search through.
if instr(dbms_utility.format_call_stack, my_name, 1, 2) > 0 then
return;
end if;
delete
from table2
where some_field = :OLD.some_field;
end table1_bt;

Go ahead & write the contents of dbms_utility.format_call_stack to DBMS_OUTPUT so that you can see what it contains.