MySQL事务和触发器 [英] MySQL transaction and trigger

问题描述

我快速浏览了MySQL手册，但没有找到有关我的问题的确切信息.这是我的问题:如果我有一个InnoDB表A，其中两个触发器由"AFTER INSERT ON A"和"AFTER UPDATE ON A"触发.更具体地说，例如:一个触发器定义为:

I quickly browsed MySQL manual but didn't find the exact information about my question. Here is my question: if I have a InnoDB table A with two triggers triggered by 'AFTER INSERT ON A' and 'AFTER UPDATE ON A'. More specifically, For example: one trigger is defined as:

CREATE TRIGGER test_trigger AFTER INSERT ON A
         FOR EACH ROW 
                      BEGIN
                         INSERT INTO B SELECT * FROM A WHERE A.col1 = NEW.col1
                      END;

您可以忽略BEGIN和END之间的查询，基本上，我的意思是该触发器将在表B中插入几行，该表B也是一个InnoDB表.

You can ignore the query between BEGIN AND END, basically I mean this trigger will insert several rows into table B which is also a InnoDB table.

现在，如果我开始交易，然后将许多行(例如:10K行)插入表A.如果没有与表A关联的触发器，则所有这些插入都是原子的，这是肯定的.现在，如果表A与几个插入/更新触发器相关联，这些触发器将许多行插入/更新到表B和/或表C等.是否所有这些插入和/或更新仍然都是原子的?

Now, if I started a transaction and then insert many rows, say: 10K rows, into table A. If there is no trigger associated with table A, all these inserts are atomic, that's for sure. Now, if table A is associated with several insert/update triggers which insert/update many rows to table B and/or table C etc.. will all these inserts and/or updates are still all atomic?

我认为它仍然是原子的，但它很难测试，并且在《手册》中找不到任何解释.任何人都可以确认吗?

I think it's still atomic, but it's kind of difficult to test and I can't find any explanations in the Manual. Anyone can confirm this?

推荐答案

按原子表示，如果触发器中的一条语句失败，则整个语句都会失败.是的-触发器是在语句的事务上下文中完成的.当然，如果没有事务，那么就没有事务上下文.

And by atomic, you mean if one statement in the trigger fails, the whole statement fails. Yes -- the trigger is done in the context of statement's transaction. And no, of course, if there is no transaction, then there is no transaction context.

对于事务表，语句失败应导致该语句执行的所有更改的回滚.触发器失败会导致语句失败，因此触发器失败也会导致回滚.对于非事务表，无法执行这种回滚，因此，尽管该语句失败，但在错误点之前执行的任何更改仍然有效.

For transactional tables, failure of a statement should cause rollback of all changes performed by the statement. Failure of a trigger causes the statement to fail, so trigger failure also causes rollback. For nontransactional tables, such rollback cannot be done, so although the statement fails, any changes performed prior to the point of the error remain in effect.

不允许您在触发器中开始交易.

And you aren't allowed to start a transaction in the trigger.

这篇关于MySQL事务和触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！