inserting data into two tables with a keyfield

[jalz]

Hi all,

As my previous thread, I've got data from text fields to populate my table. So far, Ive got 3 fields in the Customer table C_ID (Primary - Auto Increment) , FName and SName. I've got another table called Address which includes the following fields C_ID (this is going to be the keyfield that relates back to Customer ) , Address1, Address2 and Zip.

I can insert into the Customer table with the values that have been typed in on my form, but after I've 'committed' the record in the Customer table - C_ID populates/increments - I need to retrieve this and use the value in the Insert for the Address table. Am I right in thinking I need write two insert statements; write the first to the customer table, retrieve C_ID and then write the second insert to the Address or is their a 'funky' way in Livecode that will allow me to quickly write related data in the correct tables quite easily.

If I do need to write two insert statements what command do I need to use to retrieve the C_ID and I suppose it needs to go in after the revExecuteSQL conID, tSQL

Thanks all

[Mark]

Hi,

If (some) data inserted into one table is supposed to always be inserted into a second table as well, then you should add a trigger to your database that automatically takes care of this whenever your LiveCode app inserts data into the first table.

Kind regards,

Mark

[jalz]

Hi Mark,

Thanks for replying. I thought database triggers were great at moving the same data from one table to another - in a shadowtable type usage or audits. I wasn't aware I could use a database script trigger in this manor - will do some reading up on this.

Im thinking it maybe easier to do two inserts personally, as thats how Ive done things in the past in other programming environments, I just need to figure out how to retrieve the C_ID once the data has been inserted into the database.

Jalz

[Mark]

Hi Jalz,

Most of the time, you'd use revQueryDatabase to retrieve data from e.g. a SELECT statement.

Edit: revQueryDatabase selects records, revDataFromQuery retrieves data.

Best,

Mark

[jalz]

Hi Mark,

Thanks - figured out that this will get the id i'm looking for:

Code: Select all

put revDataFromQuery("","",conID,"SELECT last_insert_rowid()") into tCustID

Im suprised I dont need to tell it to use which table after the select. Seems to work without it anyway