title: MySQL After Insert Trigger - MyISAM vs InnoDB
body: <p>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.</p>
  
  <p>Let me illustrate:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>If you change the engine of <code>testTable</code> to MyISAM the original insert won't be rolled back as (I assume) MyISAM doesn't support transactions.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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?</p>