MySQL 如何在触发器失败时将有触发器的字段重置为旧值？

Question

我有一个名为 currentItem 的字段，该字段有一个触发器，该触发器调用存储过程(带有事务) sp_TransferData 来执行一些到工作表的信息传输。如果存储过程失败 - 我想恢复 currentItem 的旧值 - 因为它没有有效更改。

我正在使用 MySQL，我希望这个逻辑出现在我的触发器中 - 显然我们不想要无限循环，那么我该如何实现这一点？

触发伪代码:

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

//编辑 2016年9月16日13:00 //该表中只有 1 行，再也没有任何东西了!为简洁起见，对列列表进行了编辑。

table global_items

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

触发器在更新后或更新前在 currentItem 上，我不在乎哪个，只要它有效并且不会重新触发触发器即可:

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;

只是补充一下，这是我的触发代码中的内容，但这是不正确的。提供了表定义。

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;

当 global_items.Active_recipe 更改时，触发器会触发。移动的数据位于包及其相关表(此处简洁)中到工作表中。 global_items 表永远不会被冗长的存储过程或任何其他触发器或任何其他 SQL 代码中的任何内容所触及。它永远不会被 SQL 存储内部的任何内容修改 - 它只会被外部应用程序触及。我不确定如何在存储过程失败时将值恢复为原始值。

Answer 1

我想我可能明白你的意思了。但由于我猜你没有完全显示你的触发器，而不是存储过程，所以我只是即兴发挥并进行了调查。

我认为问题在于您的存储过程没有 OUT 限定符以使其可写。下面的内容运行良好，我认为它捕获了如何解决您的问题。

架构:

-- 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;

触发器:

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 ;

存储过程:

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 ;

测试:

反复调用此方法确认它大约有一半的时间失败

也就是说，currentItem 保留其旧值

但每次都会更改下面的更新 stmt 的 currentItem= 部分

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