-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|