MySQL Trigger after insert

Question

Are MySQL AFTER INSERT triggers always being executed directly after the INSERT statement, or is it possible that 2 inserts occur and after that 2 triggers occur?

I'm writing this trigger namely:

CREATE DEFINER=`p28004_bf4`@`localhost` TRIGGER `setId` 
AFTER INSERT ON `playerkills` 
FOR EACH ROW 
    BEGIN
        INSERT INTO globals () VALUES();
        UPDATE playerkills SET globalId = LAST_INSERT_ID() WHERE id = ROW.id;
    END

And I'm worried about what will happen if the insert statements somehow get interleaved, the globalId must always be consistent, like a global unique identifier accross multiple tables.

Globals table:

id (Primary Key, Int, Auto Increment)

Playerkills table:

id (Primary Key, int, Auto increment)
globalId (Key, Int)
etc.

Dan · Accepted Answer · 2013-11-23 16:02:56Z

1

Ultimately it doesn't matter what order concurrent commands run in this case. The LAST_INSERT_ID function is smart enough not to give you the ID inserted by a someone else's concurrent query.

As a relatively simple example, I opened two mysql sessions and created a table called globals with an autoincrement primary key, then alternated back and forth typing these commands.

    ## Session 1 ##                              ## Session 2 ##

mysql> INSERT INTO globals () VALUES ();
Query OK, 1 row affected (0.00 sec)


                                       mysql> INSERT INTO globals () VALUES ();
                                       Query OK, 1 row affected (0.00 sec)


mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

                                       mysql> SELECT LAST_INSERT_ID();
                                       +------------------+
                                       | LAST_INSERT_ID() |
                                       +------------------+
                                       |                2 |
                                       +------------------+
                                       1 row in set (0.00 sec)

answered Nov 23, 2013 at 16:02

Dan

4,55220 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

skiwi Over a year ago

I'm more worried on what will happen with a sequence of inserts. Will it be like: Insert1 -> InsertTrigger1 -> ... -> Insert2 -> InsertTrigger2? Because those may not interleave.

Dan Over a year ago

If you're on InnoDB, data from Insert1 will not be available to other queries until InsertTrigger1 completes. I'm not sure if this holds for MyISAM

Collectives™ on Stack Overflow

MySQL Trigger after insert

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related