Can I use transaction like capability in MySQL trigger

Question

I have an insert trigger which takes a set of column values from rows in table A and inserts some of them in table B and remaining in table C. I need this operation to be a transaction wherein if there is some error whilst data is inserted in table B and not C, the entire insertion operation should be rolled back.

I studied the manual and it says at the last of this page that transaction is not allowed in triggers

Is there a way to achieve what I want in mysql.

Answer 1

Yes you can, but how you do it depends on your version.

First of all, triggers are themselves transactional; in your situation, you have an insert trigger that performs two further inserts. If one of those fails, you will get your desired effect.

Consider the following example:

CREATE TABLE a (colA INT);
CREATE TABLE b (colB INT);
CREATE TABLE c (colC INT);
delimiter :
CREATE TRIGGER testtrig BEFORE INSERT ON a
  FOR EACH ROW BEGIN
    INSERT INTO b(colB) VALUES(NEW.colA);
    INSERT INTO c(banana) VALUES (NEW.colA); -- note the faulty column name
END;:
delimiter ;

Now, when I run an insert that fails, this happens:

mysql> INSERT INTO a VALUES (5);
ERROR 1054 (42S22): Unknown column 'banana' in 'field list'
mysql> SELECT * FROM a;
Empty set (0.00 sec)

This matches your desired result.

More generally, if you have logic you can use to validate your data before attempting the insert, you can fail the trigger in different ways:

• In MySQL 5.5, you can use the SIGNAL mechanism to raise an error from your trigger, thus causing it to fail the whole insert.
• Prior to MySQL 5.5, you can generate a deliberate error to fail the trigger.

I'm guessing you're using 5.0 from the link in your question, so if you need to, you can perform a deliberate error, for example deliberately insert into an invalid column, to fail a trigger. However, the situation you describe in your question is already handled transactionally, as described at the start of my answer.