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.

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.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
1  
DDL is not allowed in a trigger because they are impossible to rollback. That is why MySQL placed restrictions on Triggers and Stored Procedures ( dba.stackexchange.com/a/10663/877 , dba.stackexchange.com/a/1906/877 ) –  RolandoMySQLDBA Jun 25 '12 at 17:07
    
In addition, +1 for finding your own answer. –  RolandoMySQLDBA Jun 25 '12 at 17:08
    
@RolandoMySQLDBA Thanks for the links; I know basic SQL but I'm just getting into things like triggers and events. –  dlras2 Jun 25 '12 at 17:35

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.