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