In SQL7 and SQL2000 you can use @@IDENTITY safely assuming you have no
triggers that are causing cascading inserts. If you have triggers causing
inserts, then you need to use the select max() method as shown in original
message for SQL 7:
DECLARE @nextid
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
INSERT INTO table ... ;
SELECT @nextid = @@IDENTITY
COMMIT TRANSACTION;
Justin
> -----Original Message-----
> From: S. Isaac Dealey [mailto:info@;turnkey.to]
> Sent: Monday, October 28, 2002 12:30 PM
> To: CF-Talk
> Subject: Re: Locking and clustered servers
>
<SNIP>
>
> Depends heavily on the db server in question...
>
> SQL Server 7 Stored Procedure
>
> DECLARE @nextid
>
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>
> BEGIN TRANSACTION;
>
> INSERT INTO table ... ;
>
> SET @nextid = (SELECT TOP 1 identity FROM table ORDER
> BY identity DESC)
>
> COMMIT TRANSACTION;
>
> SQL Server 2000 Stored Procedure
>
> DECLARE @nextid;
>
> INSERT INTO table ...;
>
> SET @nextid = SCOPE_IDENTITY();
>
> I'm not familiar enough with Oracle yet to know the specific syntax.
>
> If you're absolutely certain that you won't have any triggers
> which insert
> data into other tables with identity columns in your db on
> SQL Server 7, you
> can use @@IDENTITY
>
> DECLARE @nextid;
>
> INSERT INTO table ...;
>
> SET @nextid = @@IDENTITY
>
> If you have a trigger on that table that inserts data into
> another table
> with another identity column, however, @@IDENTITY will return
> the identity
> value from the trigger, not the stored procedure.
>
> S. Isaac Dealey
> Certified Advanced ColdFusion 5 Developer
>
> www.turnkey.to
> 954-776-0046
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
