Let's say I have 3 tables, "child1", "child2" and "child3", that all have a foreign key to the table "parent". Table "parent" got a field "status", and I would that if status is set to "0" for an element, all its child are deleted. (I use trigger to detect status changes). So it's like calling "cascade delete", but without deleting the object ...
Is there a good way to do this ?
Trigger detects the change, you need to use it to fire a stored procedure. Here's a good tutorial: http://markalexanderbain.suite101.com/mysql-stored-procedures-and-triggers-a71091
Alternatively if you are using php, coldfusion or some other middle tier, you could read the record into a variable. Do delete the row, then re-insert the record. But you are better off with the stored procedure doing the work.