CODEWITHSUNDEEP
mysqlsqlatomic
Philippe
Philippe

Reputation: 2007

MySQL: Atomically inserting into a table and into a related table simultaniously

I have two tables, a TaskLists table with an automatically generated primary key and a Tasks table. A TaskList can have multiple Tasks.

How would I now insert a TaskLists row and at the same time atomically insert none to multiple rows into the Tasks table which reference the just inserted object.

Upvotes: 0

Views: 330

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

According to general relativity, nothing in this universe happens at the "same time", so you can't. :)

However, you can start a transaction, insert a row into TaskList, insert corresponding rows into Task and then commit the transaction. That would certainly be "atomic" in a sense that you couldn't (permanently) write partial data in the database.

If TaskList's ID is auto-incremented, you can get it via LAST_INSERT_ID and then use it for Task's FK.

Upvotes: 0

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

Make use of triggers that happends AFTER INSERT

CREATE TRIGGER trigger_name
AFTER INSERT
   ON TaskList FOR EACH ROW

BEGIN

   -- Insert the rows to tasks now

   INSERT INTO tasks (TaskID) VALUES (NEW.TaskId);

   -- You can run several insert statements here
END;

If you want to make use of the values from TaskList, use the NEW.columname

https://www.techonthenet.com/mysql/triggers/after_insert.php

Upvotes: 0

Related Questions