Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

migrated from stackoverflow.com May 2 '13 at 11:47

This question came from our site for professional and enthusiast programmers.

    
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. –  Mark May 2 '13 at 12:47
add comment

1 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.

share|improve this answer
    
+1 for suggesting that the foreign keys do the heavy lifting !!! –  RolandoMySQLDBA May 2 '13 at 14:41
    
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! –  Justin Shep May 2 '13 at 17:30
    
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? –  Derek Downey May 2 '13 at 19:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.