MySql Triggers to delete child records in the same table

Question

I have a table that stores parent and child records in it.

I was trying to create a trigger that would delete all child records when the parent is deleted:

Delete From tbl Where ParentId = OLD.Id

While I can save the trigger successfully, when deleting I get this error:

ERROR 1442: Can’t update table ‘tbl′ in stored function/trigger because it is already used by statement which invoked this

What am I doing wrong?

Answer 1

It appears that this is not possible:

You cannot DELETE rows in the table that activated trigger.

Some other options you might think about:

1. Write application logic that deletes the parent and child rows, and call this application logic whenever you want to delete a parent record, instead of deleting it directly.
2. Cascade delete relationship on the same table, which appears to be possible.
3. A cleanup process that routinely clears out orphaned child records.
4. (suggested by @Chris) Separate out the child records from the parent records by adding another table.

Answer 2

s. It is possible to do a Delete Cascade of child records in the same table. I found this post in the MYSQL forums had the answer. Here's how I got it to work.

1. I had to make sure that the primary id's parent was set to NULL.
2. I had to make sure that the primary id and parent id's were set to exactly the same kind of field, such as INT.
3. I also had to make sure the primary id was set to auto increment.

From the MYSQL forum:

create table edges(
  ID int PRIMARY KEY,
  parentid int,
  unique key(id, parentid),
  foreign key(parentID) references edges(ID) ON DELETE CASCADE ON UPDATE CASCADE
) engine=innodb; 

insert into edges(ID,parentID) values (1,null),(2,1),(3,1),(4,2);

Now do this and watch the parent and all children delete cascade:

delete from edges where id=2;

select * from edges will then show two records left.  Record 1 and record 3.

This was huge for my project, a gallery where users could create albums inside of albums inside of albums.