来自表的SQL ID将相同的ID发送到每个事务的所有其他coldfusion条目 [英] SQL ID from table posts same ID to all other coldfusion entries per transaction

问题描述

如何从我的事务表中触发我的ID在每个会话的每个表的所有条目上相同？我试图插入大量信息到许多表，但试图保持所有的表由TransactionID连接在一起，并努力如何创建TransactionID与第一个条目，然后获取该条目，并在所有其他表条目上使用它。 p>

（这是我的插入创建事务，这应该在我的事务表中自动创建一个ID，因为ID是我在事务表中的主键，IsIdentity为yes，增量为一个）

 < cfquery datasource =Titlesbymailname =InsertEntry> 
 INSERT INTO dbo.Transaction（Type，OwnerType）
 VALUES（
< cfqueryparam value ='NonLeased'cfsqltype ='cf_sql_varchar'/> 
，< cfqueryparam value = 'Owner'cfsqltype ='cf_sql_varchar'/> 
）
< / cfquery> 
  

然后创建事务表：

ID：1

类型：NonLeased

OwnerType：所有者

我想知道如何保持同一个事务ID （如果表存在）

 < cfquery datasource =Titlesbymailname = CustomerInsertEntry> 
 INSERT INTO dbo.Customer（TransactionID，ID，FirstName，LastName）
 VALUES（
< cfqueryparam value = **'（事务表中的ID）'** 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> 
  

这是对我来说是全新的，并且做了大量的研究，我只是碰到sql命令的触发器

解决方案

在使用时， cfquery ，您可以使用results属性根据运行的查询返回信息。此信息的一部分是 GENERATEDKEY 。
然后，您可以将此 GENERATEDKEY 分配给一个变量，然后在代码中使用。

首先需要将结果属性添加到您的第一个 cfquery 。

  < cfquery datasource =Titlesbymailname =InsertEntryresult =transactionResult> 
 INSERT INTO dbo.Transaction（Type，OwnerType）
 VALUES（
< cfqueryparam value ='NonLeased'cfsqltype ='cf_sql_varchar'/> 
，< cfqueryparam value = 'Owner'cfsqltype ='cf_sql_varchar'/> 
）
< / cfquery>然后分配一个变量以使用 GENERATEDKEY   


$ b <从结果（或直接使用结果变量）

ie < cfset theID = transactionResult.GENERATEDKEY>

然后在代码中的任何其他位置使用此变量。 p>

 < cfquery datasource =Titlesbymailname =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> 
  

How do I trigger my ID from my Transaction table to be the same on all entries to each table per session? I am trying to insert a lot of information to many tables but trying to keep all tables linked together by TransactionID and am struggling on how it creates the TransactionID with the first entry then grabbing that entry and using it on all other table entries.

(This is my insert to create the Transaction. This should automatically create an ID in my Transaction table since ID is my primary key in the transaction table and IsIdentity is yes and increment of one)

<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>

This then creates the transaction table:
ID: 1
Type: NonLeased
OwnerType: Owner

I am trying to figure out how I can keep that same transaction ID to be inserted with my next entries to 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 have done lots of research I just keep coming across sql commands of triggers and all but have no idea how that applies with my coldfusion set up like this.

解决方案

When using cfquery you can use the results attribute to return information based on the query that was run. One part of this information is GENERATEDKEY. You can then assign this GENERATEDKEY to a variable and use it later in your code.

So first you need to add the result attribute to your first 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>

Then assign a variable to use the GENERATEDKEY from the result (or use the result variable directly)

i.e. <cfset theID = transactionResult.GENERATEDKEY>

Then you use this variable anywhere else in your code.

<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>

这篇关于来自表的SQL ID将相同的ID发送到每个事务的所有其他coldfusion条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！