CODEWITHSUNDEEP
mysqlsqltriggers
skiwi
skiwi

Reputation: 69249

MySQL Trigger after insert

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:

Playerkills table:

Upvotes: 0

Views: 984

Answers (1)

Dan
Dan

Reputation: 4502

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)

Upvotes: 1

Related Questions