mysql 触发器中使用事务,我可以在MySQL触发器中使用类似事务的功能吗

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.

解决方案

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.