MySQL 事务和触发器

2022-01-01 00:00:00 transactions mysql triggers innodb

我快速浏览了 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 AND END 之间的查询，基本上我的意思是这个触发器将向表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.

相关文章