0

I got the following database layout - as an example:

table_1 <--- FK --- table_2 <--- FK --- table_3
   |                  |                   
trigger             trigger
   |                  |
table_1_deleted     table_2_deleted

So table_1 is the parent of table_2 which is parent of table_3. The foreign keys are defined are as ON DELETE CASCADE. So if I delete a record from table_1, all children in table_2 and table_3 are deleted as well.

What about the triggers? Both are BEFORE DELETE and they are inserting a new record in the table_x_deleted so that I can see which record has been deleted. When I delete a record from table_1, a new row will be inserted into table_1_deleted. Same is true for table_2.

My problem is the foreign Key. Image you are deleting a record from table_1. Then a new row is inserted into table_1_deleted and due to the FK the child-record in table_2 is also deleted. Therefore the trigger on table_2 should be executed and a new record should be inserted into table_2_deleted. But it isn't. And I can not figure out why. If I delete a single record from table_2 (not table_1), the the row is correctly inserted into table_2_deleted.

Matthias
  • 5,574
  • 8
  • 61
  • 121
  • 1
    http://stackoverflow.com/questions/6041064/trigger-calls-in-cascade-deleting-mysql – Wasim Jul 26 '14 at 18:16
  • perfect. thanks. this one was also very good: http://wijayatony.wordpress.com/2012/07/08/mysql-trigger-not-activated-on-delete-cascade/ – Matthias Jul 26 '14 at 18:20

0 Answers0