Mysql – Trigger not firing when deleting a record from stored procedure MySql

MySQLstored-procedurestrigger

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!

Best Answer

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.

Related Question