MySQL后插入触发器- MyISAM vs InnoDB

I'm trying to get an after insert trigger to NOT roll back an insert done to an innodb table. MyISAM does not seem to have this problem.

我正在尝试获得一个after insert触发器，以避免回滚对innodb表执行的insert done。MyISAM似乎没有这个问题。

Let me illustrate:

让我说明:

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

If you change the engine of testTable to MyISAM the original insert won't be rolled back as (I assume) MyISAM doesn't support transactions.

如果将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

Question: Is there a way to make after insert triggers for InnoDB tables preserve the original insert if there's an error in the trigger?

问:是否有一种方法可以在InnoDB表的insert触发器之后保存原始的insert，如果触发器中出现了错误?

2 个解决方案

#1

Yes, the different behaviour is related to whether the engine 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.

对于事务表，语句的失败应该导致语句执行的所有更改的回滚。触发器失败会导致语句失败，因此触发器失败也会导致回滚。对于非事务性表，不能执行这种回滚，因此尽管语句失败，但是在错误点之前执行的任何更改仍然有效。

You can declare a CONTINUE handler for this specific error :

您可以为这个特定的错误声明一个CONTINUE处理程序:

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 ;

#2

I concur with everything said here so far. Obviously non transactional storage engines won't roll back. However, you can change your trigger to a BEFORE instead of an AFTER trigger and achieve the same result as in the InnoDB example, I.e. the changes not committed as so:

我同意到目前为止所说的一切。显然非事务性存储引擎不会回滚。但是，您可以将您的触发器更改为a BEFORE而不是AFTER触发器，并获得与InnoDB示例相同的结果，即未提交的更改如下:

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 BEFORE 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 occur!');
SELECT COUNT(1) FROM __testTable; # 1

#1