Ran into a snag almost immediately after posting my explanation of the VPN database search modifications. It turns out that ON DELETE CASCADE foreign key deletions do NOT activate triggers on the cascaded table (see the MySQL site: http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html). This presents a problem because the AFTER DELETE triggers on poems_to_poems and poems_to_authors handle the modification of the poem_search.related_poems and poem_search.authors fields, rather than triggers on the poems and authors tables themselves. However, when a poem or author is deleted directly (rather than just deleting a row in the poems_to_poems or poems_to_authors table), since the corresponding row in the lookup table is deleted as part of the CASCADE, the AFTER DELETE trigger does not fire and thus the poem_search table is not updated. So, I need DELETE triggers on the authors and poems tables directly to handle this limitation.

But finding a solution is tricky. Using the authors table as an example, If I put an AFTER DELETE trigger on the authors table to handle the modification of the poem_search.authors field, by the time the trigger fires, all of the poems_to_authors rows will be gone and thus the trigger won't know which poems to update. Likewise, if the authors table has a BEFORE DELETE trigger, all of the rows in the poems_to_authors table will still exist and so the deleted author will remain in the poem_search table.

Unfortunately, there's no ultimately satisfying solution that doesn't require a total rewrite of the triggers and procedures. I'd like to keep using the author_loop and related_poem_loop procedures to update the poem_search.authors and poem_search.related_poems fields, so my solution is to modify the two loops to allow for an "exclude ID" to be passed. For example, passing an author ID as the "exclude ID" to author_loop will cause the procedure to ignore that ID when building the content for the poem_search.authors table. Only minor modifications are required to the procedure code. The new author_loop procedure looks like this:

DELIMITER $$

CREATE PROCEDURE author_loop
(IN poem_id int(11), OUT author_content text, IN exclude_id int(11))
READS SQL DATA
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE author_content_temp text;
    DECLARE author_id int(11);
    DECLARE authors CURSOR FOR SELECT pta_au_id FROM poems_to_authors WHERE pta_po_id = poem_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN authors;
    
        read_loop: LOOP
            
            FETCH authors INTO author_id;
            
            IF done THEN
                LEAVE read_loop;
            END IF;
            
            IF author_id != exclude_id THEN
                CALL parse_author(author_id, author_content_temp);
                SET author_content = CONCAT_WS('; ', author_content, author_content_temp);
            END IF;
            
        END LOOP;
                
    CLOSE authors;
    
END $$
DELIMITER ;

I added an extra IN exclude_id int(11) parameter at the top, and then, during the loop, check that author_id does not match exclude_id (IF author_id > 0 AND author_id != exclude_id THEN ...). The most tedious part of this modification was changing ALL of the calls to author_loop to take into account the extra parameter by passing either an ID or 0 (passing NULL seems to break the procedure for some reason). I had to modify the poem_to_authors_delete poems_to_authors_insert triggers:

DELIMITER $$
CREATE TRIGGER poem_to_authors_delete AFTER DELETE ON poems_to_authors FOR EACH ROW
BEGIN
    DECLARE author_content text;
    
    CALL author_loop(old.pta_po_id, author_content, 0);
    UPDATE poem_search SET authors = author_content WHERE id = old.pta_po_id;

END $$
DELIMITER ;


DELIMITER $$
CREATE TRIGGER poem_to_authors_insert AFTER INSERT ON poems_to_authors FOR EACH ROW
BEGIN
    DECLARE author_content text;

    CALL author_loop(new.pta_po_id, author_content, 0);
    UPDATE poem_search SET authors = author_content WHERE id = new.pta_po_id;

END $$
DELIMITER ;

With that change made, I put a BEFORE DELETE trigger on the authors table to rebuild the poem_search.authors fields with the to-be-deleted author passed as the exclude_id. I also had to modify the author_poem_loop procedure to allow the BEFORE DELETE trigger to pass the exclude ID through it and into the author_loop procedure. The updated procedure followed by the new trigger:

DELIMITER $$
CREATE PROCEDURE author_poem_loop
(IN author_id int(11), OUT author_poem_content text, IN exclude_id int(11))
READS SQL DATA
MODIFIES SQL DATA
BEGIN

    DECLARE done INT DEFAULT 0;
    DECLARE author_poem_content text;
    DECLARE author_poem_id int(11);
    DECLARE author_poems CURSOR FOR SELECT pta_po_id FROM poems_to_authors WHERE pta_au_id = author_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN author_poems;
        read_loop: LOOP
            
            FETCH author_poems INTO author_poem_id;
            
            IF done THEN
                LEAVE read_loop;
            END IF;
            
            IF author_poem_id > 0 THEN
                CALL author_loop(author_poem_id, author_poem_content, exclude_id);
                UPDATE poem_search SET authors = author_poem_content WHERE id = author_poem_id;
            END IF;
            
        END LOOP;
    CLOSE author_poems;

END $$
DELIMITER ;


DELIMITER $$
CREATE TRIGGER author_delete BEFORE DELETE ON authors FOR EACH ROW
BEGIN
    DECLARE author_poem_content text;
    
    CALL author_poem_loop(old.au_id, author_poem_content, old.au_id);

END $$
DELIMITER ;

So, the BEFORE DELETE trigger simply passes the to-be-deleted author id as the exclude ID so it's not included when building the poem_search.authors content. Bingo! Works like a charm.

I made similar changes to the poem_to_poems_delete trigger, poem_to_poems_insert trigger, related_poem procedure, related_poem_lookup_loop procedure, :

DELIMITER $$
CREATE TRIGGER poem_to_poems_delete AFTER DELETE ON poems_to_poems FOR EACH ROW
BEGIN
    DECLARE related_poem_content text;
    
    CALL related_poem_loop(old.ptp_po1_id, related_poem_content, 0);
    UPDATE poem_search SET related_poems = related_poem_content WHERE id = old.ptp_po1_id;

END $$
DELIMITER ;


DELIMITER $$
CREATE TRIGGER poem_to_poems_insert AFTER INSERT ON poems_to_poems FOR EACH ROW
BEGIN
    DECLARE related_poem_content text;
    
    CALL related_poem_loop(new.ptp_po1_id, related_poem_content, 0);
    UPDATE poem_search SET related_poems = related_poem_content WHERE id = new.ptp_po1_id;

END $$
DELIMITER ;

DELIMITER $$

CREATE PROCEDURE related_poem_loop
(IN poem_id int(11), OUT related_poem_content text, IN exclude_id int(11))
READS SQL DATA
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE related_poem_content_temp text;
    DECLARE related_poem_id int(11);
    DECLARE related_poems CURSOR FOR SELECT ptp_po2_id FROM poems_to_poems WHERE ptp_po1_id = poem_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN related_poems;
    
        read_loop: LOOP
            
            FETCH related_poems INTO related_poem_id;
            
            IF done THEN
                LEAVE read_loop;
            END IF;
            
            IF related_poem_id != exclude_id THEN
                CALL parse_related_poem(related_poem_id, related_poem_content_temp);
                SET related_poem_content = CONCAT_WS('; ', related_poem_content, related_poem_content_temp);
            END IF;
            
        END LOOP;
                
    CLOSE related_poems;    

END $$

DELIMITER ;


DELIMITER $$
CREATE PROCEDURE related_poem_lookup_loop
(IN poem_id int(11), OUT related_poem_content text, IN exclude_id int(11))
READS SQL DATA
MODIFIES SQL DATA
BEGIN

    DECLARE done INT DEFAULT 0;
    DECLARE related_poem_content text;
    DECLARE related_poem_id int(11);
    DECLARE related_poems CURSOR FOR SELECT ptp_po1_id FROM poems_to_poems WHERE ptp_po2_id = poem_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN related_poems;
        read_loop: LOOP
            
            FETCH related_poems INTO related_poem_id;
            
            IF done THEN
                LEAVE read_loop;
            END IF;
            
            IF related_poem_id > 0 THEN
                CALL related_poem_loop(related_poem_id, related_poem_content, exclude_id);
                UPDATE poem_search SET related_poems = related_poem_content WHERE id = related_poem_id;
            END IF;
            
        END LOOP;
    CLOSE related_poems;

END $$
DELIMITER ;

I couldn't just make a related_poem_delete trigger for the BEFORE DELETE action to update poem_search.related_poems when a related poem is deleted, though, because there's already a BEFORE DELETE trigger on the poems table and MySQL only allows one trigger per type per table. So, I had to modify the existing poem_delete trigger:

DELIMITER $$
CREATE TRIGGER poem_delete BEFORE DELETE ON poems FOR EACH ROW
BEGIN
    DECLARE related_poem_content text;
    
    CALL related_poem_loop(old.po_id, related_poem_content, old.po_id);
    DELETE FROM poem_search WHERE id = old.po_id;

END $$
DELIMITER ;

First the trigger handles the updating of the poem_search.related_poems fields via the related_poem_loop procedure (and passing old.po_id as the exclude id). Then it deletes the poem's poem_search record, which is the original behavior of the trigger.