Is there a simple PHP alternative to MySQL Triggers?

PHP Version: 5.1.6-20060908
MySQL Version: 5.0.51

I'm working on a project with several layers of related data.

Parent Table
|_Child Table
| |_Grand Child Table
| |_Great Grand Child Table
|_Second Child
|
etc.

When I delete an entry in any of these tables I want to delete all associated entries in their sub (child) tables based on a key.

I belevive MySQL Triggers are the best solution; However, I am on a shared server and I do not have permission to create triggers.

I could write PHP functions with a series of loops for each table but I'm not sure how "elegant" that will be given the number of tables and potential combinations of tasks (for example, if I delete a Parent entry I would need to delete from child, grand child, great grand child and second child, but if i delete a child i only need to delete from th grand child and great grand child).

Is there an existing alternative solution to Triggers through PHP? If so, can anyone direct me to a good resource?

I'm not looking for someone to do the work for me, I just need a nudge in the right direction.

Many thanks,

Andrew

If using InnoDB as table type, with relevent foreign keys defined, an ON DELETE CASCADE should do this for you.

Hi,

yes, you should definitely do this with foreign keys. If your hoster doesn't support that, grab a few dollars and switch to a better hoster.

Trying to reimplement this functionality with a home made script will create a total mess of half deleted data -- simply because of the delay. Not to mention that it's horrible with regard to software architecture.

Thanks Simon and Jacques,

This looks like a good solution. I am going to switch to innoDB.

I'll have to do some more reading though. I set up a couple of test tables with the ON DELETE CASCADE set for the parent table linked to the index on the child table and twoissues occur:

1) when I delete a parent through phpMyAdmin the children are not deleted

2) when I try to add a new parent I get an error "Cannot add or update a child row: a foreign key constraint fails "

I'm chalking this up to my inexperience and will continue to read up on potential solutions. I haven't made it all the way through the link you provided - the answer's probbly there somewhere.

Thanks again for the direction you provided. It's very helpful.

Cheers

The child tables need a foreign key pointing to the parent table, because you want them to refer to a valid record in the parent table. If you do it the other way round, you cannot add new rows in the parent table, because the IDs won't be found in the child tables, of course.

Brilliant! That was it. I had it backwards.

Cheers,

one more question about this:

what happens if i have something like this:

table1 (id1,.....)
join_table(id1, id2)
table2(id2....)

if i now delete something from table1, will table2 row delete also or just table1 and join_table?

That's a question probably better suited for the mySQL forum.

If you don't define table2 as having a foreign key to table 1, then it won't be deleted. The row in the join_table would be due to the id1<->id1 relation, but there is no such relation directly between table1 and table2 so no delete would occur.