Mysql – How to trigger this stored procedure on insert

mysql-5.5stored-procedurestrigger

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.

Best Answer

I figured out that the following lines of code weren't allowed to run in a trigger:

CREATE TABLE IF NOT EXISTS interactionrecord
      (`datediff` INT);
   TRUNCATE TABLE interactionrecord;

I had had them in there for debugging purposes and forgot to remove them.

Once I removed them, I could run the code as a trigger, until I tried updating the error table, since apparently updating the table the trigger is on isn't allowed either.

What I'm doing now is a trigger which simply inserts new error Id into a small table, then having an event run every minute or so, calling my stored procedure on all the newly inserted errors.

Related Question