Well,
you would have to write the trigger in DDL, using QA or in Enterprise
Manager. This method would be very useful I think, but maintenance of a
system can be a problem that has triggers ( I forget they are there :-0
).
You could do all Of the inserts in the one query :-) , and use
transactions if you want to.
<CFQUERY>
BEGIN TRANSACTION T1
DECLARE @NEW_ID AS NUMERIC
INSERT INTO TABLE () VALUES ()
IF @@ERROR > 0
ROLLBACK TRANSACTION T1
SET @NEW_ID = (SELECT @@IDENTITY)
INSERT INTO TABLE2 (...) VALUES (@NEW_ID, 1)
IF @ERROR > 0
ROLLBACK TRANSACTION T1
COMMIT TRANSACTION T1
</CFQUERY>
>>> [EMAIL PROTECTED] 28/01/2005 10:58:48 am >>>
Scott,
Can I setup the second table insert via a trigger using CF code or do
I
set them up in Enterprise Manager? MS SQL 2000.
Thoughts or simple syntax solution if you have time. Or is it better
using
the above solution?
Thanks Chris
Knowledge is only Power if it is used and applied.....
> Hi,
>
> In a single CFQuery
>
> <CFQUERY name="insert_stuff">
> insert into whatever
> (
> ..
> )
> values
> (
> ..
> )
> SELECT @@IDENTITY AS NEW_ID
> </CFQUERY>
>
> #insert_stuff.new_id#
>
> or if there are triggers on your table, use scope_identity()
>
> Using MS SQL 2000......
>
> >>> [EMAIL PROTECTED] 27/01/2005 7:49:03 pm >>>
> I want to insert the unique id generated by the first insert query
> into
> the same column (id_contact) in the third cfquery statement.
>
> Syntax from the second cfquery statement is throwing an error...
>
> has anyone got a better way of getting this done(or the proper way)?
>
> Thanks
>
> Chris
>
> *****************************************************************
>
> <cfquery name="sorted" datasource="#APPLICATION.DB#"
> USERNAME="#APPLICATION.UN#" PASSWORD="#APPLICATION.PW#">
> INSERT INTO #APPLICATION.DBPRE#Subscribe
> (FirstName,
> LastName,
> Company,
> StreetAddress,
> StreetAddress2,
> City,
> State,
> ZipCode,
> Country,
> PhoneNumber,
> SubscriberEmail,
> <!---UserLists,--->
> UserConfirmed,
> CreatedDate,
> IsEmValid)
> VALUES
> ('#Trim(form.FirstName)#',
> '#Trim(form.LastName)#',
> '#Trim(COMPANY)#',
> '#Trim(form.StreetAddress)#',
> '#Trim(STREETADDRESS2)#',
> '#Trim(form.City)#',
> '#Trim(form.State)#',
> '#Trim(form.ZipCode)#',
> '#Trim(form.Country)#',
> '#Trim(form.PhoneNumber)#',
> '#Trim(form.SubscriberEmail)#',
> <!---'#Trim(form.UserLists)#',--->
> 1,
> '#DateFormat(Now(), "yyyy-mm-dd")#',
> 1)
> </cfquery>
>
> <cfquery name="sorted2" datasource="#APPLICATION.DB#"
> USERNAME="#APPLICATION.UN#" PASSWORD="#APPLICATION.PW#">
> SELECT *
> FROM #APPLICATION.DBPRE#Subscribe
> WHERE id_contact = MAX(id_contact)
> </cfquery>
>
> <cfset same_id= #sorted2.id_contact#>
>
> <cfquery name="peace" datasource="#APPLICATION.DB#"
> USERNAME="#APPLICATION.UN#" PASSWORD="#APPLICATION.PW#">
> INSERT INTO #APPLICATION.DBPRE#ContactLog
> (#same_id#,
> id_campaign,)
> VALUES
> (#sorted.id_contact#,
> 1)
> </cfquery>
>
> ---
> You are currently subscribed to cfaussie as:
> [EMAIL PROTECTED]
> To unsubscribe send a blank email to
> [EMAIL PROTECTED]
> Aussie Macromedia Developers: http://lists.daemon.com.au/
---
You are currently subscribed to cfaussie as:
[EMAIL PROTECTED]
To unsubscribe send a blank email to
[EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
---
You are currently subscribed to cfaussie as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
