DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2001

    Delete from child table based on parent table


    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
    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?


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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.