MySQL After Insert Trigger - 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.

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.

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?

Solution

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 :

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 ;