mysql trigger help

[ngreenwood6]

I was trying to create a trigger when I delete something from a table. Here is my table structure:

 

id

parent_id

name

 

The parent_id field is linked to the same table's id field. So the parent_id of any item in this table is going to be another rows id unless it is the parent (0). So something like this:

 

<-ROW->

1

0

Dog

 

<-ROW->

2

1

German Sheperd

 

Now when I delete dog I also need it to delete German sheperd because that item doesnt exist without its parent. I know when I run the delete I can just perform another delete for the children but I wanted to see if it can be done with a trigger. When I put the delete query in there it gives me this error : "Can't update table 'categories' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.". Anyone know if this is possible or not? Any help is appreciated.

[mikosiko]

no... is not possible (one of the trigger/functions limitations)... the error is saying you that

 

"Can't update table 'categories' in stored function/trigger because it is already used by statement which invoked this stored function/trigger."