I’ve added foreign keys to my MySQL database after reading this great Sitepoint article and am trying to figure out the best way to implement audit tables. At first, I was going to use triggers, but from what I understand from the docs, if I have a cascading delete, where I delete a parent table via an actual sql statement (i.e. in my PHP code), then the children tables will delete, but the trigger can only apply to the parent table (not sure if parent/children is the correct terminology, but hopefully it makes sense!).
Is there a “clean” way to implement both the trigger functionality and the foreign keys either with MySQL or with PHP? A big picture approach versus coding specifics would be the most helpful at this point.
I’m going to answer my own post, as I hope that it will help another user with the same question. After thinking about this for a few days, I realized that the way to have both triggers and foreign keys is to use a restricted as opposed to a cascading delete.
Basically, using PHP, I’ll code each of the deletes of the children table, in which case I can also apply a trigger to save the information to my audit table. Though I don’t have the advantage of the minimally coded cascading delete, I still have the very important advantage of referential integrity as afforded by the use of foreign keys.
And, in retrospect, I’m sure that this was a super noob question for most, but hopefully there’s at least one other noob out there who will find my self-posted solution to be helpful.