mysql - SQL Trigger statement -

given

table1:

id|name

table2:

id|table1_id|table3_id

table3:

id|checked(type=boolean)

now want delete row in table2 when corresponding row of table1 deleted , corresponding table3 row checked = true.

which sql statement have use ?

you'll need 2 triggers, 1 detecting checked set 1, 1 checking deletion table1. these bit "quick , dirty", should work on decently recent mysql version.

delimiter //  create trigger trig_table1 after delete on table1  each row begin   delete table2.*     table2   join table3      on table3_id = table3.id    , table3.checked = 1    , table1_id = old.id; end; //  create trigger trig_table3 after update on table3 each row begin   delete table2.*    table2   left join table3      on table3.id = old.id    , table3.checked = 0   left join table1     on table1_id = table1.id    , table3_id = old.id   table2.table3_id = old.id     , table3.id null     , table1.id null; end; // 

an sqlfiddle test with.