Copy multiple records using foreign keys

Question

Suppose I have two tabels, 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 to relates to A.id. Now, suppose there is one record in A (1, 'foo', 'bar') and 2 records in B (1, 1, 'test') and (2, 1, 'test1').

My question is, is there a standard method of copying the record in A, and, at the same time copying all the records from B that relate to A. So suppose I create a new record in A (2, 'foo', 'bar') that's based on (1, 'foo', 'bar'), is there some sort of method that creates two new records in B (3, 2, 'test') and (4, 2, 'test1)?

I've never used triggers before, is this the correct moment to start doing that? Or is this a very stupid question?

Answer 1

Hello Lex,

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 am not aware of. Copying rows is not the problem (assuming that 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 ID to duplicate the records 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 am a bit sceptical about the LAST_INSERT_ID(). Anyway, I don't think it can be done with just one statement.

Let me know how it goes

Tom