Double insert using value from previous insert

Question

I have a simple data structure which uses multi-table inheritance:

Table: Event
cols: int **id**, int game_tick

Table Event_A
cols: int *id*, int dmg_taken

Table Event_B
cols: int *id*, float x, float y, float z

.. and other specific event tables

The id of the specific events are foreign key constraints that is tied to the id of an event, which is auto-incremented.

So the problem is that if I want to create a record in Event_B, I have to first create a record in event and then use the resulting ID as input to a new query. Is it possible for me to either put both statement into one, or force MySQL to utilize some kind of propagation that allows me to simple insert a record of Event_B and have the parent event record auto-generated for me?

Would it be possible to use a trigger on the specific event tables that creates a new event and then uses the resulting ID as the default value for the id in the table?

* UPDATE *

I tried solving this issue using the following nested SQL statements and last_insert_id() (as suggested by the answer to this question):

INSERT INTO event (game_tick) VALUES (10); 
select @id:=id as id from event where id = last_insert_id();
INSERT INTO event_b(id, x, y, z) VALUES (@id, '2', '2', '2');

Which works perfectly when I execute it using the MySQL workbench. However, I am working in SourceMod and it appears that its MySQL extension does not support nested queries. As such, when the query is sent to the DB driver, it fails with a syntax error.

I could perhaps just perform the three different queries, but I am unsure if that would create a race condition in terms of what queries gets scheduled when, since a lot of queries can potentially be fired at the same time.

My next approach was to add it as an BEFORE INSERT trigger in MySQL, so on Table_B I have the following:

CREATE DEFINER = CURRENT_USER TRIGGER `MyDatabase`.`event_b_BEFORE_INSERT` 
BEFORE INSERT ON `event_b` FOR EACH ROW
BEGIN
    INSERT INTO event (game_tick) VALUES (10); 
    select @id:=id as id into @id FROM event where id = last_insert_id();
    IF (NEW.id IS NULL) THEN
      SET NEW.id = @id;
    END IF;
END

However, my issue with this is that I am inserting a dummy value (10) into the parent table, event. I could just add the game_tick column to each specific event type, but if possible I would like to keep the shared values for all events in the parent type table. How would this be possible?

As mentioned in the answer to the following question the idea should be to have the common fields in the parent table, but I don't see how I can make that work, unless I also have the game-tick stored somewhere else.

score 1 · Answer 1 · answered Mar 11 '16 at 00:20

You might need to use

LAST_INSERT_ID()

An example code would be:

INSERT INTO event (game_tick) VALUES (10); 
INSERT INTO event_b(id, x, y, z) VALUES (LAST_INSERT_ID(), '2', '2', '2');

This function will get the last inserted ID for the current transaction. If you want to use the last inserted id in multiple tables you will need to store it temporarily like this.

INSERT INTO event (game_tick) VALUES (10); 

SET @lastid = LAST_INSERT_ID();

INSERT INTO event_b(id, x, y, z) VALUES (@lastid, '2', '2', '2');
INSERT INTO event_c(id, x, y, z) VALUES (@lastid, '3', '3', '3');

More information can be found here http://dev.mysql.com/doc/refman/5.7/en/getting-unique-id.html

Thank you for the reply, as you can see in my update, this was the path I started exploring after seeing another question here on SO. However, as mentioned, I would really like to avoid nested queries. Is it possible to implement this functionality using triggers instead? I am trying that now, but without any luck so far. — Simon Langhoff, Mar 11 '16 at 00:23

Double insert using value from previous insert

1 Answers1