Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2003
    Posts
    81

    Unanswered: Delete-triggers that works both ways

    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?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    What about foreign keys with cascading deletes?

  3. #3
    Join Date
    Jun 2003
    Posts
    81
    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.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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.

  5. #5
    Join Date
    Jun 2003
    Posts
    81
    That is correct. But unfortunately I won't be able to make inserts afterwards - because there is no associated parent !

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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)

  7. #7
    Join Date
    Jun 2003
    Posts
    81
    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 )

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    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.

  9. #9
    Join Date
    Dec 2003
    Posts
    1,074
    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;

  10. #10
    Join Date
    Jun 2003
    Posts
    81
    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 :-)

  11. #11
    Join Date
    Dec 2003
    Posts
    1,074
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •