MySQL How to reset the field to old value that has a trigger when the trigger fails?

Question

I have a field called currentItem , this field has a trigger that calls a stored procedure (with transaction) sp_TransferData to perform some transfers of information to a worktable. If the stored procedure fails - I would like to restore the old value of currentItem - as it has not effectively changed.

I am using MySQL and I would like this logic to be in my trigger - obviously we do not want an endless loop so how do I accomplish this ?

Trigger Pseudo Code :

Call sp_TransferData(@ResultCode);
Select @ResultCode into Result;
If Result < 0 THEN
thisField Value = OLD.Value;
End If;

// EDIT 9-16-2016 13:00 // There is only 1 Row in this table never any thing more! Column list edited for brevity.

table global_items

Id INT PK, 
lsize INT, 
wsize INT, 
currentItem INT

Trigger is on currentItem After Update or Before I do not care which as long as it works and does not refire the trigger:

If the value has changed CALL sp_TransferData(@ResultCode);
If (SELECT @ResultCode) < 0 THEN
Reset currentItem to old value but do not cycle the trigger since we are only resetting it.
EndIf;

Just to add this is what I have in my trigger code which is not correct. The Table definitions are supplied.

BEGIN

     IF NEW.currentItem <> OLD.currentItem THEN

     call sp_CurrentItemChanged(NEW.currentItem, @ResultCode, @ResultMsg);

        IF ((Select @ResultCode) < 0) THEN

         NEW.currentItem = OLD.currentItem;

        END IF;

     END IF;

END



    CREATE TABLE working_table (
  Id int(11) NOT NULL AUTO_INCREMENT,
  Mbf float DEFAULT NULL,
  Width float DEFAULT NULL,
  Pulse int(11) DEFAULT NULL,
  PRIMARY KEY (Id)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci
ROW_FORMAT = DYNAMIC;

CREATE TABLE recipe (
  Id int(11) NOT NULL AUTO_INCREMENT,
  Name varchar(80) NOT NULL DEFAULT 'UnAssigned',
  IsDefault tinyint(1) DEFAULT 0,
  PRIMARY KEY (Id),
  UNIQUE INDEX Id_UNIQUE (Id),
  UNIQUE INDEX Name_UNIQUE (Name)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci
ROW_FORMAT = DYNAMIC;


CREATE TABLE Packs (
  Id int(11) NOT NULL AUTO_INCREMENT,
  Name varchar(45) NOT NULL DEFAULT 'UNDEFINED',
  Width float NOT NULL DEFAULT 0,
  Pulse int(11) NOT NULL DEFAULT 0,
  Mbf float NOT NULL DEFAULT 0,
  RecipeID int(11) NOT NULL DEFAULT 0,
  SetupID int(11) DEFAULT 1,
  PRIMARY KEY (Id),
  INDEX SetupID_ndx (SetupID),
  INDEX FK_PackRecipeID_Recipe_ID_idx (RecipeID),
  INDEX FK_RecipeID_PackS_idx (RecipeID),
  CONSTRAINT FK_PackRecipeID_Recipe_ID FOREIGN KEY (RecipeID)
  REFERENCES recipe (Id) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci
ROW_FORMAT = DYNAMIC;

CREATE TABLE global_items (
  Id int(11) NOT NULL AUTO_INCREMENT,
  PackSys_Count int(11) DEFAULT NULL,
  Active_Recipe int(11) DEFAULT 1,
  PRIMARY KEY (Id)
)
ENGINE = INNODB
AUTO_INCREMENT = 2
CHARACTER SET utf8
COLLATE utf8_general_ci
ROW_FORMAT = DYNAMIC;

When global_items.Active_recipe changes the trigger fires .. The data that is moved is in packs and its related tables (brevity here) to the working_table. The global_items table is NEVER touched by anything in the lengthy Stored Procedure or any other triggers or any other sql code. It is never modified by anything internal to the SQL storage - it is touched only by an outside applications. I am not sure how I restore the value to the original value on failure of the stored procedure.

Answer 1

I think I might be understanding what you are getting at. But since you did not show your trigger entirely I guess, and not the Stored Proc, I just winged it and investigated.

I think the problem is that your stored proc does not have an OUT qualifier to it to make it write-able. The below works fine and I think it captures how to solve your issue.

Schema:

-- drop table global_items;
create table global_items
(   Id INT primary key, 
    lsize INT not null, 
    wsize INT not null, 
    currentItem INT not null,
    theCount int not null
);
insert global_items(Id,lsize,wsize,currentItem,theCount) VALUES
(1,1,1,100,0);

select * from global_items;

Trigger:

DROP TRIGGER IF EXISTS giBeforeUpdate;
DELIMITER $$
CREATE TRIGGER giBeforeUpdate
BEFORE UPDATE
   ON global_items FOR EACH ROW
BEGIN
    DECLARE tRet INT;
    SET tRet=0;
    SET NEW.theCount=OLD.theCount+1;
    CALL uspDoSomething7(tRet);
    IF tRet=1 THEN
        -- stored proc said FAILURE
        SET NEW.currentItem=OLD.currentItem;
    END IF;
END;$$
DELIMITER ;

Stored Procedure:

DROP PROCEDURE IF EXISTS uspDoSomething7;
DELIMITER $$
CREATE PROCEDURE uspDoSomething7(OUT retVal INT)
BEGIN
    DECLARE rndNum INT;
    SET rndNum=FLOOR(RAND()*2)+1; -- random number 1 or 2

    -- sets retVal to 1 on FAILURE
    IF rndNum=2 THEN
        SET retVal=1; -- FAIL
    ELSE
        SET retVal=0; -- SUCCESS
    END IF;
END$$
DELIMITER ;

Test:

Repeatedly call this confirm that it fails roughly half the time

that is, currentItem retains its old value

but chg the update stmt below each time for the currentItem= part of it

update global_items set currentItem=410,lsize=2 where Id=1;
select * from global_items;