I have a table of error messages to which errors will be inserted. I also have a table defining interactions between errors. For example, when a "Power Restored" error occurs, it clears any previous "Power Lost" errors. ("Error" being used loosely to describe any enumerable diagnostic message.)
To do this, I've defined the following stored procedure:
BEGIN
-- Declare local variables
DECLARE done BIT DEFAULT 0;
DECLARE target SMALLINT(5) UNSIGNED DEFAULT '0';
DECLARE `action` ENUM('Clear','Raise','SetSeverity');
DECLARE severity TINYINT(3) UNSIGNED DEFAULT NULL;
DECLARE timespan INT(11) DEFAULT NULL;
DECLARE cutoff TIMESTAMP;
-- Declare the cursor
DECLARE interactions CURSOR FOR
SELECT TargetErrNo, errorinteraction.`Action`, errorinteraction.Severity, errorinteraction.TimeSpan
FROM errorinteraction WHERE ActorErrNo=NEW.Number ORDER BY Priority DESC;
-- Declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
CREATE TABLE IF NOT EXISTS interactionrecord
(`datediff` INT);
TRUNCATE TABLE interactionrecord;
-- Open the cursor
OPEN interactions;
-- Loop through all rows
REPEAT
-- Get
FETCH interactions INTO target, `action`, severity, timespan;
IF NOT done THEN
-- Process
IF timespan IS NULL THEN
IF `action`='Clear' THEN
UPDATE error SET Cleared=NOW(), UpdatedBy=NEW.Id WHERE Number=target AND Cleared IS NULL;
ELSEIF `action`='Raise' THEN
UPDATE error SET Cleared=NULL, UpdatedBy=NEW.Id WHERE Number=target AND Cleared IS NOT NULL;
ELSEIF `action`='SetSeverity' AND severity IS NOT NULL THEN
UPDATE error SET error.Severity=severity, UpdatedBy=NEW.Id WHERE Number=target AND error.Severity!=severity;
END IF;
ELSE
SET cutoff=DATE_SUB(NEW.Reported, INTERVAL timespan SECOND);
IF `action`='Clear' THEN
UPDATE error SET Cleared=NOW(), UpdatedBy=NEW.Id WHERE Number=target AND Cleared IS NULL AND error.Reported>=cutoff;
ELSEIF `action`='Raise' THEN
UPDATE error SET Cleared=NULL, UpdatedBy=NEW.Id WHERE Number=target AND Cleared IS NOT NULL AND error.Reported>=cutoff;
ELSEIF `action`='SetSeverity' AND severity IS NOT NULL THEN
UPDATE error SET error.Severity=severity, UpdatedBy=NEW.Id WHERE Number=target AND error.Severity!=severity AND error.Reported>=cutoff;
END IF;
END IF;
END IF;
-- End of loop
UNTIL done END REPEAT;
-- Close the cursor
CLOSE interactions;
END
This works perfectly when I insert a new error by hand, then call this function passing the new error's Id.
But when I try and insert a trigger to call it automatically, MySQL begins throwing Error 1422 at me. I've tried both FOR EACH ROW CALL updateerror(NEW.Id); and copying the stored procedure code into the trigger directly, replacing a few of the variables with their respective NEW columns. Should I simply leave this as a stored procedure and assume users are responsible to call it? Is there a better way to do this?
My exact MySQL version is 5.5.25.
