我可以在MySQL触发器中使用类似事务的功能吗 [英] Can I use transaction like capability in MySQL trigger

问题描述

我有一个插入触发器，该触发器从表A中的行中获取一组列值，并将其中一些插入表B中，其余保留在表C中.是在表B中插入而不是在表C中插入的，则应回滚整个插入操作.

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

有没有一种方法可以实现我想要的mysql.

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.

请考虑以下示例:

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)

这符合您想要的结果.

更一般而言，如果您有逻辑，可以在尝试插入之前用来验证数据，则可以通过以下不同方式使触发器失败:

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:

• 在MySQL 5.5中，您可以使用 SIGNAL 机制会引发触发器错误，从而导致整个插入失败.
• 在MySQL 5.5之前，您可以生成故意的错误以使触发器失败.

• 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.

我猜您正在使用问题链接中的5.0，因此，如果需要，您可以执行故意的错误(例如，故意插入无效列中)，以使触发器失败.但是，您在问题中描述的情况已经按照我的答案开头所述进行了事务处理.

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.

这篇关于我可以在MySQL触发器中使用类似事务的功能吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！