我有一些触发器BEFORE INSERT,AFTER DELETE在表上.如何确定触发器是否失败,然后我的查询会回滚？

我的意思是我想确定,查询和触发器都可以工作,或者它们都不起作用.触发器交易是什么？

解决方法:

我可以通过存储过程显示这个.这个概念从这个answer从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 
);

触发

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

关于触发器的快速说明：如果您尝试插入年龄= 13,则将设置信号.这将启动交易的最终ROLLBACK.

请注意,DELIMITERS非常重要.要修改上述内容,请突出显示POINT A和POINT B之间的所有文本并执行它.该块将使用DELIMITER后方需要的那种疼痛来执行跌落和娱乐.没有DELIMITER,错误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://*.com/a/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中的所有代码以对上述内容进行编辑.这次是存储过程,但类似于之前的创建触发器.

含义,类似于使用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());

Error Code: 1644. tableA_BeforeIns bombed due to age=13 0.000 sec

清理

drop database trigTranTest;

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