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!
id_primary= id_primary_live;) so DELETE never happens.