Delete-triggers that works both ways

kfc@vd.dk · #1 (**permalink**) 05-11-09, 04:29

I have two tables which are dependant on each other. If I delete one record in table A, I also want to delete the corresponding record in table B and vice versa.

When I make triggers for that intention, they (of course) mutates.

How do I get my wish to come through?

shammat · #2 (**permalink**) 05-11-09, 05:00

What about foreign keys with cascading deletes?

kfc@vd.dk · #3 (**permalink**) 05-11-09, 05:53

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.

shammat · #4 (**permalink**) 05-11-09, 06:09

Quote:

Originally Posted by kfc@vd.dk

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.

If you can define foreign keys in both directions then deletion would work in both directions.

kfc@vd.dk · #5 (**permalink**) 05-11-09, 06:56

That is correct. But unfortunately I won't be able to make inserts afterwards - because there is no associated parent !

shammat · #6 (**permalink**) 05-11-09, 07:59

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)

kfc@vd.dk · #7 (**permalink**) 05-11-09, 08:21

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.

Example:

Table A (ID number, FILE varchar2(20));
data:
1,'file1.txt'
2,'file2.txt'
3,'file3.txt'

Table B (ID number, PATH varchar2(100));
data:
1,'c:\temp\'
2,'d:\data\'
3,'c:\temp\sub\'

If I say: DELETE FROM A WHERE ID=2
I need the record in both tables to be deleted.

If I say DELETE FROM B WHERE ID=2
Should give me the same result (both records deleted)

But I still need to be able to say
INSERT INTO A (4,'newfile.txt')

My trigger will then make the record (4,'') in the table B (and I could update this table afterwards to (4,'c:\newdir\)

If I make the both-way-constraints with cascading deletes - I won't be able to make the insert part.

If you understand my problem, I would be very pleased if you could help me back by showing me an example of how-to-do

)

beilstwh · #8 (**permalink**) 05-11-09, 10:15

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.

chuck_forbes · #9 (**permalink**) 05-11-09, 11:07

If you keep things the way you have them, you can use a package spec variable as a global variable to determine where the DELETE call is coming from.

I've seen this done with a stack with the TRIGGER name in it.

Code:

create trigger table1_bt
before delete
on table1
for each row
  my_name varchar2(30) := 'TABLE1_PG';
begin
  if stack_pg.in_stack('TRIGGERS', my_name) then
    return;
  end if;

  stack_pg.push('TRIGGERS', 'table1_bt');

  delete 
  from table2
  where some_field = :OLD.some_field;

  stack_pg.pop('TRIGGERS', 'table1_bt');
  
exception
  when others then
     stack_pg.destroy('TRIGGERS');
  
end table1_bt;

kfc@vd.dk · #10 (**permalink**) 05-12-09, 03:16

It sounds like a pretty good idea to be able to determine where the DELETE call is coming from. But unfortunately I can't figure out how to create such package. What is the stack_pg?

I am feeling closer to the finishing line - so please help me further :-)

chuck_forbes · #11 (**permalink**) 05-12-09, 10:56

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.

--=Chuck

Internet Services

Web Development

Digital Marketing

Consumer Tech