CODEWITHSUNDEEP
mysqltransactionstriggers
Abhishek
Abhishek

Reputation: 157

Can I use transaction like capability in MySQL trigger

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.

Upvotes: 5

Views: 3784

Answers (2)

Gerard ONeill
Gerard ONeill

Reputation: 4102

You get what you asked for by default -- any error in a trigger causes the statement to fail. So if there is a transaction on the statement, you get a rollback of the data to just before that statement. If there is no transaction, then you don't.

Which is probably why creating or ending a transaction is not allowed in a trigger.

So no need for a stored procedure. In fact, the stored procedure you call from the trigger might cause an error if it tries to create a transaction.

But feel free to use a stored procedure to start a transaction before doing the action that causes the trigger.

Upvotes: 0

Jeremy Smyth
Jeremy Smyth

Reputation: 23503

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.

Upvotes: 4

Related Questions