The SQL identifier from the table reports the same identifier to all other cold transaction entries

Question

How do I configure the identifier from my transaction table the same way for all records in each table per session? I try to insert a lot of information into many tables, but I try to ensure that all tables are linked by TransactionID, and I fight for how it creates a TransactionID with the first record, and then captures this record and uses it in all other table records.

(This is my insertion for creating a transaction. This should automatically create an identifier in my transaction table, since the ID is my primary key in the transaction table, and IsIdentity - yes, and with step 1)

<cfquery datasource="Titlesbymail" name="InsertEntry">
 INSERT INTO dbo.Transaction (Type, OwnerType)
 VALUES (
    <cfqueryparam value='NonLeased' cfsqltype='cf_sql_varchar' />
   , <cfqueryparam value='Owner' cfsqltype='cf_sql_varchar' />
 )
</cfquery>

Then the transaction table is created:
ID: 1 | Type: NonLeased
OwnerType: Owner

I am trying to figure out how I can keep the same transaction ID that will be inserted with my subsequent records into my other 6 tables (if the table exists)

<cfquery datasource="Titlesbymail" name="CustomerInsertEntry">
 INSERT INTO dbo.Customer (TransactionID, ID, FirstName, LastName)
 VALUES (
    <cfqueryparam value= **'(ID from Transaction Table)'** cfsqltype='cf_sql_int' />
   , <cfqueryparam value='1' cfsqltype='cf_sql_int' />
   , <cfqueryparam value='#session.checkout.info.firstname_1#' cfsqltype='cf_sql_varchar' />
   , <cfqueryparam value='#session.checkout.info.lastname_1#' cfsqltype='cf_sql_varchar' />
 )
</cfquery>

This is completely new to me, and I have done a lot of research, and I just continue to encounter trigger teams and everyone, but I don’t know how this applies to my ColdFusion configured this way.

Answer 1

cfquery . GENERATEDKEY. GENERATEDKEY .

, cfquery.

<cfquery datasource="Titlesbymail" name="InsertEntry" result="transactionResult">
 INSERT INTO dbo.Transaction (Type, OwnerType)
 VALUES (
    <cfqueryparam value='NonLeased' cfsqltype='cf_sql_varchar' />
   , <cfqueryparam value='Owner' cfsqltype='cf_sql_varchar' />
 )
</cfquery>

GENERATEDKEY ( )

. <cfset theID = transactionResult.GENERATEDKEY>

- .

<cfquery datasource="Titlesbymail" name="CustomerInsertEntry">
 INSERT INTO dbo.Customer (TransactionID, ID, FirstName, LastName)
 VALUES (
    <cfqueryparam value='#theID#' cfsqltype='cf_sql_integer' />
   , <cfqueryparam value='1' cfsqltype='cf_sql_integer' />
   , <cfqueryparam value='#session.checkout.info.firstname_1#' cfsqltype='cf_sql_varchar' />
   , <cfqueryparam value='#session.checkout.info.lastname_1#' cfsqltype='cf_sql_varchar' />
 )
</cfquery>