Copy multiple records using foreign keys

Question

Suppose I have two tables: A and B, each with three columns (A.id, A.title, A.text and B.id, B.a_id, B.text). B.a_id is a foreign key related to A.id. Suppose that in (1, 'foo', 'bar') there is one entry and 2 entries in B (1, 1, 'test') and (2, 1, 'test1').

My question is: is there a standard way to copy a record to and at the same time copy all the records from B that belong to A. Therefore, suppose I create a new record in (2, 'foo', 'bar') based on ( 1, 'foo', 'bar'), is there any method that creates two new entries in B (3, 2, 'test') and (4, 2, test1)?

I have never used triggers before, is this the right moment to start doing this? Or is this a very stupid question?

Answer 1

This is not a stupid question. However, I believe that this is not possible with pure SQL or only with some exotic syntax that I don't know about. Copying strings is not a problem (assuming id is auto_increment):

insert into A (title, text) select title, text from A where id = XY

However, then you need to find the last insert identifier in order to duplicate the entries in B. Let's see:

insert into B (a_id, text) select LAST_INSERT_ID(), text from B where a_id = XY

Hm ... maybe this works, but I'm a little skeptical about LAST_INSERT_ID (). In any case, I do not think that this can be done with just one statement.

Let me know how it works.

Tom