Are triggers transaction?

Question

I have some triggers BEFORE INSERT, AFTER DELETE on a table. How do I make sure if trigger fails, then my query gets rollback?

I mean I want to be sure, either both query and trigger work or none of them doesn't work. So are triggers transaction?

Answer 1

From the mysql documentation:

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.

Answer 2

I can show this via a stored procedure. The concept was lifted from this answer from wchiquito. I believe you will find this to be a more exhaustive answer. It is just an example. Make necessary changes for you particular needs (other trigger types) etc. How one performs the mysql trigger signal firing outside of not using stored procedures is anyone's guess. So if you are not willing or able to do the stored procedure, read no further.

Note that any drops or truncates are left in for your convenience and remmed out.

Schema

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

A quick note on the trigger: if you attempt to insert age=13, the signal will be set. This will set in motion the ultimate ROLLBACK of the Transaction.

Please note that DELIMITERS are important. To modify the above, highlight ALL text between your POINT A and POINT B and execute it. That block will perform the drop and recreation using the sort of pain in the rear need of DELIMITERs. Without the DELIMITER, Error 1064 is imminent. Translation: it won't work. What won't work? The part of creating the trigger to begin with.

Stored Procedure

-- 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://stackoverflow.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

A quick note here: once the START TRANSACTION has occured, we will get to COMMIT unless our trigger signals a SQLSTATE, thus causing a ROLLBACK.

As mentioned before, highlight and execute all the code inside of POINT A and POINT B to make edits to the above. This time for stored procs, but similar to the create trigger before.

Meaning, similar to trigger modifications with the safe wrappings of a DELIMITER block. Otherwise, Error 1064 is imminent, and the stored proc will not be created.

Test

Note, the following remmed out truncates are left here for your convenience during Test.

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

Results

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

Results are as expected honoring transaction handling and not allowing inserts with age=13. Sure, it is arbitrary, but we have to test it somehow.

Call via Mysql Workbench

One last visual. Run an insert directly from Mysql Workbench with age=13

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

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

Cleanup

drop database trigTranTest;

The test database has been dropped and is gone.