Manually call cascade delete?

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 s开发者_运维技巧tatus 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.

delete all children with associated foreign key to the parent..

authorname = Bill, authorid = 5

we need to delete all books by bill

DELETE FROM books WHERE authorid=5

EDIT: sorry, re-read.. you could implement this logic on UPDATE of the status