触发器是否属于事务？

Question

我有一些在表上的 BEFORE INSERT，AFTER DELETE 触发器。如果触发器失败，如何确保我的查询回滚？

我的意思是我想要确定，不论是查询还是触发器都能工作，或者它们两个都不能工作。因此，触发器是否是事务性的？

- Martin AJ

---

可能是MySQL事务和触发器的重复问题。 - sstan

Answer 1

来自mysql文档：

对于事务表，语句的失败应该导致语句执行的所有更改回滚。触发器的失败会导致语句失败，因此触发器的失败也会导致回滚。

- Samuel Renold

---

“对于事务性表”…您具体是什么意思？我如何判断我的表是否为事务性表？ - Martin AJ

事务性表是支持事务的表。当语句的效果可以提交或回滚时，该语句在事务中执行。 - Samuel Renold

@MartinAJ 对于MySQL，这取决于您的表的引擎。例如，InnoDB是事务性的，而MyISAM则不是。 - RnMss

Answer 2

我可以通过存储过程展示这个。这个概念来源于wchiquito的回答，我相信您会发现这是一个更详尽的回答。这只是一个例子。根据您的特定需求（其他触发器类型等），进行必要的更改。如果不使用存储过程执行mysql触发器信号触发，则任何人都无法猜测。所以，如果您不愿意或无法使用存储过程，请不要继续阅读。

请注意，任何删除或截断都是为了您的方便而留下的，并被注释掉。

架构

create database trigTranTest;   -- creates a separate database for testing
use trigTranTest;   -- use that database

-- drop table tableA;
create table tableA
(   id int auto_increment primary key,
    something varchar(100) not null,
    age int not null,    -- do not accept unlucky 13
    myDT datetime not null 
);

-- drop table tableB;
create table tableB
(   -- simply to demonstrate multiple tables in a transaction and that they are honored as a group (ie: Transaction)
    -- all or nothing basically
    id int auto_increment primary key,
    something varchar(100) not null,
    myDT datetime not null 
);

-- drop table auditInfoNotInTrans;
create table auditInfoNotInTrans
(   -- a boring table outside of Transaction to show an attempt was made
    id int auto_increment primary key,
    debugInfo varchar(100) not null,
    myDT datetime not null 
);

Trigger

-- POINT A
drop trigger if exists tableA_BeforeIns;
DELIMITER $$
create trigger tableA_BeforeIns before insert on tableA
for each row
begin
    if new.age = 13 then
        -- disallow unlucky age=13 for inserts. Wait another year.
        signal sqlstate '45000' set message_text = "tableA_BeforeIns bombed due to age=13";
    end if;
end$$
DELIMITER ;
-- POINT B

触发器的一点提示：如果你尝试插入age=13，信号将被设置。这将引发事务的最终回滚。

请注意DELIMITERS很重要。要修改上述内容，请突出显示POINT A和POINT B之间的所有文本并执行。该块将使用DELIMITER的繁琐操作执行删除和重新创建。没有DELIMITER，Error 1064即将到来。翻译：它不会工作。什么不起作用？首先创建触发器的部分。

存储过程

-- POINT A
drop procedure if exists insertChunk;
DELIMITER $$
CREATE PROCEDURE insertChunk(pSomething varchar(100), pAge  int)
    -- takes two parameters, a string for a thing, and an age 
BEGIN
    -- idea lifted from https://dev59.com/e2Ij5IYBdhLWcg3wxnyd#19908197 by user wchiquito
    -- so spread the appreciation there
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;

    -- the following happens outside of the Transaction
    insert auditInfoNotInTrans(debugInfo,myDT) values(pSomething,now());

    -- now our Transaction part begins
    START TRANSACTION;
    insert tableA(something,age,myDT) values (pSomething,pAge,now());   -- pAge being unlucky 13 fails via the Trigger
    IF `_rollback` THEN
        ROLLBACK;
    ELSE
        insert tableB(something,myDT) values (pSomething,now());
        COMMIT;
    END IF;
END$$
DELIMITER ;
-- POINT B

这里需要注意：一旦发生START TRANSACTION，我们将执行COMMIT，除非触发器发出SQLSTATE信号，从而导致ROLLBACK。

如前所述，请突出并执行POINT A和POINT B中的所有代码，以对上述内容进行编辑。这次是针对存储过程，但与之前的create trigger类似。

也就是说，类似于使用DELIMITER块的安全包装来修改触发器。否则，将会出现错误1064，并且无法创建存储过程。

测试

请注意，在测试期间，以下被注释掉的截断将保留在此处，以方便您使用。

-- truncate tableA;

-- truncate tableB;

-- truncate auditInfoNotInTrans;
call insertChunk('frog',1);
call insertChunk('lizard',13);  -- force a Trigger failure with the unlucky 13
call insertChunk('snake',2);

结果

select * from auditInfoNotInTrans;
+----+-----------+---------------------+
| id | debugInfo | myDT                |
+----+-----------+---------------------+
|  1 | frog      | 2016-06-10 15:09:02 |
|  2 | lizard    | 2016-06-10 15:09:06 |
|  3 | snake     | 2016-06-10 15:09:08 |
+----+-----------+---------------------+

select * from tableA;
+----+-----------+-----+---------------------+
| id | something | age | myDT                |
+----+-----------+-----+---------------------+
|  1 | frog      |   1 | 2016-06-10 15:09:02 |
|  2 | snake     |   2 | 2016-06-10 15:09:08 |
+----+-----------+-----+---------------------+

select * from tableB;
+----+-----------+---------------------+
| id | something | myDT                |
+----+-----------+---------------------+
|  1 | frog      | 2016-06-10 15:09:02 |
|  2 | snake     | 2016-06-10 15:09:08 |
+----+-----------+---------------------+

结果符合预期，遵循事务处理并不允许插入年龄为13的数据。当然，这是任意的，但我们必须以某种方式进行测试。

通过Mysql Workbench调用

最后一个可视化操作。在Mysql Workbench中直接运行插入操作，年龄为13。

insert tableA(something,age,myDT) values ('turtle',13,now());

错误代码：1644。由于age=13，tableA_BeforeIns遭到破坏 0.000秒

清理

drop database trigTranTest;

测试数据库已被删除并且已经消失。

- Drew

---

那就意味着我们不能从触发器中调用带有事务的过程吗？ - Taurus Silver