Delete from child table based on parent table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Delete from child table based on parent table

  1. #1
    Join Date
    Jan 2001
    Posts
    216

    Delete from child table based on parent table

    Hi,

    We have 3 tables, A, B and C. A and B are in a 1-many relationship and B and C are in 1-1 relationship. One record in A may have about 10,000 records in B and each of those B records will have 1 record in C.

    Now, when a row in A is deleted, we need to cascade delete rows from B and C. We dont have cascade delete turned on due to its incompatibility with multimaster replication (not supported). Hence we do it using triggers as follows:

    delete trigger on A says:
    delete from B where a_id = ld.a_id;

    delete trigger on B says:
    delete from C where b_id = ld.b_id;

    This works, except that its very slow. Delete trigger on B is called 10,000 times.

    Hence I want to modify delete trigger on A to delete from C first, then delete from B.
    delete from C where b_id in (select b_id from B where a_id = ld.a_id);

    Is this the most efficient way to do this? Or are there better ways of doing it. Please let me know.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Why do you need 2 triggers - one for A and another for B?

    Did you test with only one trigger (that is on A) in which you delete rows in C, and finally B?

    Tamil

Posting Permissions

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



Click Here to Expand Forum to Full Width