RomanLaw: using triggers in mySQL 5
mySQL 5 has both stored procedures and triggers, and I remembered having noticed this when I was half-way through coding the delete functionality. I've never used them before, so I had to do a bit of research to find example code, but they're quite straightforward, and take a lot of the hassle out of coding SQL statements for relational tables. I've added three of them to the RomanLaw db. For the record, here's a walkthrough of one of them:
CREATE TRIGGER delKeyword BEFORE DELETE ON Keywords FOR EACH ROW DELETE FROM keywordToInsc WHERE keywordId = OLD.id
The first line creates a trigger called "delKeyword" which runs BEFORE any DELETE operation on the Keywords table. (You can also specify triggers to run after an operation.) The next two lines constitute the code to be run. the FOR EACH ROW selector specifies that the trigger runs for each row which is to be deleted; then the last line specifies the code to run. In this case, it deletes any records in the keywordToInsc table which have the same value in the keywordId field as the id field of the row in the Keywords table which is being deleted. In other words, this cleans out all records in the associated keywordsToInsc table that use the keyword item being deleted from the Keywords table.
Based on cursory testing through phpMyAdmin, this seems to work fine on our mysqldev server installation. The AJAX delete code can now be much simpler, and pass less info over the network.