插入触发器后的 MySQL - MyISAM 与 InnoDB

Question

我正在尝试获取插入后触发器，以不回滚对 innodb 表完成的插入。 MyISAM好像没有这个问题。

让我举例说明:

CREATE TABLE `testTable` (
  `id` int(10) AUTO_INCREMENT,
  `data` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB; #Engine supports transactions

CREATE TABLE `dummyTable` (
  `id` int(10) AUTO_INCREMENT,
  `data` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


DELIMITER $$
CREATE TRIGGER triggerTest AFTER INSERT ON `testTable`
FOR EACH ROW
BEGIN
  INSERT INTO dummyTable VALUES(1, 2, 3, 4); #This will throw a column count error
END;$$
DELIMITER ;


INSERT INTO testTable(data) VALUES('This insert will be rolled back');
SELECT COUNT(1) FROM testTable; # 0

如果您将 testTable 的引擎更改为 MyISAM，则原始插入将不会回滚，因为(我假设)MyISAM 不支持事务。

CREATE TABLE `testTable` (
  `id` int(10) AUTO_INCREMENT,
  `data` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM; #Engine does NOT support transactions

CREATE TABLE `dummyTable` (
  `id` int(10) AUTO_INCREMENT,
  `data` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DELIMITER $$
CREATE TRIGGER triggerTest AFTER INSERT ON `testTable`
FOR EACH ROW
BEGIN
  INSERT INTO dummyTable VALUES(1, 2, 3, 4); #This will throw a column count error
END;$$
DELIMITER ;

INSERT INTO testTable(data) VALUES('This insert will not be rolled back');
SELECT COUNT(1) FROM testTable; # 1

问题:有没有办法让 InnoDB 表的插入触发器在触发器出错时保留原始插入？

Answer 1

是的，不同的行为与引擎是否相关 supports transactions or not :

For transactional tables, failure of a statement should cause rollback of all changes performed by the statement. Failure of a trigger causes the statement to fail, so trigger failure also causes rollback. For nontransactional tables, such rollback cannot be done, so although the statement fails, any changes performed prior to the point of the error remain in effect.

您可以 declare a CONTINUE handler对于 this specific error :

DELIMITER $$
CREATE TRIGGER triggerTest AFTER INSERT ON `testTable`
FOR EACH ROW
BEGIN
  DECLARE CONTINUE HANDLER
      FOR SQLSTATE '21S01' -- "Column count doesn't match value count"
      BEGIN END; -- do nothing (but continue)
  INSERT INTO dummyTable VALUES(1, 2, 3, 4);
END $$
DELIMITER ;