Trigger not firing when deleting a record from stored procedure MySql

Question

Bare with me, as I'm fairly new to stored procedures / triggers.

I have the following Stored Procedure:

DELIMITER $$
CREATE DEFINER=`username`@`serveraddress` PROCEDURE `TASK_APPROVE`(IN idtask INT, IN idrevised INT, IN idapproved INT)
ROOT:BEGIN
 DECLARE SQL_EXCEPTION INT DEFAULT 0;
 DECLARE rpi_number_draft varchar(20);
 DECLARE id_primary_draft INT;
 DECLARE FETCH_STATUS INTEGER DEFAULT 0;
 DECLARE id_primary_live, revision_live, num_rows INT;
 DECLARE counter INT DEFAULT 0;
 #Create Cursor to iterate through all rpis in draft
 DECLARE rpiDraftCursor CURSOR FOR 
 SELECT `id_primary`, `number` 
 FROM `rpi_procedures` 
 WHERE `id_task` = idtask;
 #Create Loop Handler to determine when all records in the cursor have been iterated through 
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET FETCH_STATUS = 100;
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING SET SQL_EXCEPTION = 100;
 SELECT `id_primary`, `number` 
 FROM `rpi_procedures` 
 WHERE `id_task` = idtask;
 SELECT FOUND_ROWS() INTO num_rows;
 SET counter = 0;
 OPEN rpiDraftCursor;
 #Loop through all records in the rpiDraftCursor
 draft_loop:WHILE (FETCH_STATUS = 0) DO
 #Get First Record From Cursor
 FETCH NEXT FROM rpiDraftCursor
 INTO id_primary_draft, rpi_number_draft;
 BEGIN
 DECLARE live_rows_returned INT;
 START TRANSACTION;
 SELECT `id_primary`, `revision`
 FROM `rpi_procedures`
 WHERE `number` = rpi_number_draft
 AND `id_status` = 1 
 INTO id_primary_live, revision_live;
 SELECT FOUND_ROWS() INTO live_rows_returned;
 #live_rows_returned will equal zero if its a new rpi
 IF live_rows_returned = 0 THEN
 SET id_primary_live = id_primary_draft;
 SET revision_live = -1;
 END IF;
 IF (counter < num_rows) THEN
 UPDATE `rpi_procedures`
 SET `id_previous` = id_primary_live,
 `revision` = revision_live + 1,
 `id_revised` = idrevised,
 `id_approved` = idapproved,
 `id_status` = 1,
 `id_task` = 0
 WHERE `id_primary` = id_primary_draft; 
 IF live_rows_returned > 0 THEN
 DELETE 
 FROM `rpi_procedures`
 WHERE `id_primary` = id_primary_live; 
 END IF;
 END IF;
 IF SQL_EXCEPTION = 100 THEN
 ROLLBACK;
 END IF;
 SET counter = counter + 1;
 COMMIT;
 END; 
 #If Loop Handler = 100 Exit Loop
 IF FETCH_STATUS = 100 THEN
 LEAVE draft_loop;
 END IF;
 End WHILE draft_loop;
 CLOSE rpiDraftCursor;
 SET FETCH_STATUS = 0; 
END$$
DELIMITER ;

And I have the following trigger:

DELIMITER $$
DROP TRIGGER /*!50032 IF EXISTS */ `RPI_PROCEDURE_DELETE`$$
CREATE DEFINER=`username`@`serveraddress` TRIGGER `database`.`RPI_PROCEDURE_DELETE` AFTER DELETE
 ON `database`.`rpi_procedures`
 FOR EACH ROW BEGIN
 DELETE FROM `rpi_procedure_sequences` WHERE `id_procedure` = OLD.id_primary;
 DELETE FROM `rpi_procedure_parts_dimensions` WHERE `id_procedure` = OLD.id_primary;
 DELETE FROM `rpi_procedure_parts` WHERE `id_procedure` = OLD.id_primary;
 DELETE FROM `rpi_contract_additional` WHERE `id_procedure` = OLD.id_primary;
 DELETE FROM `rpi_contract_general` WHERE `id_procedure` = OLD.id_primary;
 DELETE FROM `rpi_contract_material` WHERE `id_procedure` = OLD.id_primary;
 DELETE FROM `rpi_contract_models` WHERE `id_procedure` = OLD.id_primary;
 DELETE FROM `rpi_contract_parts` WHERE `id_procedure` = OLD.id_primary;
 DELETE FROM `rpi_contract_specialty` WHERE `id_procedure` = OLD.id_primary;
 DELETE FROM `rpi_contract_subcontract` WHERE `id_procedure` = OLD.id_primary;
 END;
$$
DELIMITER ;

When a record is deleted in the stored proc, I need the trigger to fire to remove the references to that record in multiple tables. I know the trigger works because when I manually delete a record from the rpi_procedures table, the trigger does indeed fire and removes the records from the required tables. Is there something I am missing in the stored proc to get the trigger to work?

Thanks!

• user23243: I'm unsure what's wrong and just guessing... but hope it helps somehow. So... I can see four potential options here: 1-Trigger is cached and didn't get updated(flushed) for some reason; 2-you are on clustered mysql servers and by that, trigger is not present in every server instance(create it in all servers); 3-your SP's "IF live_ro... > 0 THEN" is never bigger than zero so Trigger don't fire; 4-WHERE clause always false (id_primary = id_primary_live;) so DELETE never happens.

Answer 1

It shouldn't matter where the DELETE happens, the trigger should fire from the stored procedure. This begs the question, are you sure the DELETE is actually happening within the stored procedure, ie does the COMMIT actually happen?

As an alternative to your approach, if the tables are InnoDB, you could use Foreign Keys on each of your tables that reference your rpi_procedures.id_primary column to have an ON DELETE CASCADE clause.

• RolandoMySQLDBA: +1 for suggesting that the foreign keys do the heavy lifting !!!
• Justin Shep: I am positive the delete is occurring (tested that functionality extensively). The person who originally designed / created the DB created the tables as MyISAM tables otherwise I would have definitely taken advantage of setting the Foreign Key constraints. We needed to get it done quick so I just threw the trigger code in the Stored Proc and called it a day. Not ideal, or how I would like to do it, but we needed it functional. Thanks!
• Derek Downey: That's interesting. It might be doing something funky since it is MyISAM, which doesn't support transactions (which you use in the stored procedure). Could you test to see if it works outside the START TRANSACTION...COMMIT block, but still within the stored procedure?