将表的默认值插入另一个表

Question

Simplified version:

I have a table named books (book_id, book_title) where book_id is the primary key and of type serial (so it is assigned automatically without me inserting it).

I also have another table named book_version (id, book_id, book_version) where book_id is foreign key to the first table.

With a form I take the book title and version. I insert the title into the first table and have to insert the generated book_id along with version into the second table.

How can I do that without having 3 separate queries (one insert for first table, one select to get it's id and one insert for the second table)?

The idea here is that the same book may have been published several times with slight changes, so i want to keep track of different versions.

Answer 1

Use an after insert trigger on the first table:

http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

An after insert trigger can reference the inserted row and then you only have to run 1 statement to insert the version.

You only have to insert the first row via PHP, and your trigger is installed in the database and does the work after that.

That is the best way to do it, IMO.