@@Identity being over-written by Insert Trigger in stored procedure.

Hi All

I have a problem with an existing stored procedure that is used to insert a new entry to a table (using an Insert statement).

I have been using the @.@.Identity global variable to return the identity column (id column) back to the calling routine. This has worked fine for years until recently an after insert Trigger has been added to the table being updated.

Now the @.@.Identity is returning the identity value of the trigger that was called instead of the original table insert.

Does anyone know how I can code around this issue (without using a select statement within my stored proc, as these have been known to cause locks in the past).

Thank in advance.

Eamon.Look at SCOPE_IDENTITY and/or IDENT_CURRENT

Regards,

hmscott|||Thank you very much hmscott. SCOPE_IDENTITY works a treat!!!

@@IDENTITY and thread safety

I am currently using @.@.Identity to retreive the Identity value for the PK field in a table that I am inserting data into. Essentially, my code looks like this:
SqlCommand cmd=new SqlCommand("Insert into table(... ;Select @.@.Identity from table",conn);
string identity=cmd.ExecuteScalar();

Testing this myself, it works fine, but I am worried as to how thread safe this is in a real-world environment? (i.e. with multiple users clicking at it). Is there a guaranteed way to make this thread safe- wrap it in a transaction maybe?
I think you should use scope_identity()|||I would search the online books on @.@.Identity. I'm sure the subject istouched upon. I seem to recall something about @.@.Identity applying tothe current connection or context but I forgot the details. In anycase, @.@.Identity is so widely used that if it wasn't thread safe we'dall be in serious trouble.
|||scope_identity()
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_6n8p.asp
@.@.Identity
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp
@.@.Identity is thread safe, but scope_identity() is the way to go.|||

Everyone,

Thanks for the help. JHouse, those articles are great. Just for the benefit of others reading this thread, the key difference between the @.@.IDENTITY and Scope_Identity() is the @.@.IDENTITY returns the last identity value of any table your batch or procedure inserts into - implicit or explicit - while Scope_Identity() is explicit only. So if you insert into tableA that has a trigger that inserts into tableB. @.@.Identity will return the identity from tableB, and Scope_Identity will return the identity from tableA.

@@identity

hi,

I was wondering if someone could help me out with this stored procedure I have. I am trying to execute a transaction in one of my sps and am getting pk violations on 'OrderID'.
This where i encounter this error:

SELECT
 @.OrderID = @.@.Identity
/* Copy items from given shopping cart to OrdersDetail table for given OrderID*/
INSERT INTO OrderDetails
(
 OrderID,
 ProductID,
 Quantity,
 UnitCost
)
SELECT
 @.OrderID,
 ShoppingCart.ProductID,
 ShoppingCart.Quantity,
 Prices.UnitCost
FROM
ShoppingCart INNER JOIN
 Prices ON ShoppingCart.ProductID = Prices.ProductID
WHERE
 CartID = @.CartID

is there any way to rewrite this statement so that I can put it in the form insert()values(). ?is OrderID an autonumbering field?

if so - you shouldn't include it in your INSERT statement|||YEs it is ! Why do you not recommend putting it in the insert stetment ?

Anyways, I realized that my error wasn't really in the stored procedure, but in the function calling it.

Thanks.|||SQL will put the identity into the table automatically, what you're trying to do is over-write the value with the same number again...hence the duplicate key problem. So like the poster said, don't do it, get the help out and read what an identity column acutally is and how to use it.|||I'm not really overwriting the @.@.identity value, but assigning it to a variable. I then use the value of that variable in an insert statement on a totally separate table.
I solved my primary key issue and it wasn't related to the sql transaction i posted here, ....there was a logic error in my code.

So now even though its all working, you still think that the sql statement I'm using is incorrect ?|||Sorry I admit I was assuming the code that went before your example. The only suggest I would make would be to consider using SCOPE_IDENTITY rather than IDENTIY, tends to be safer in the long run.sql

@@identity

i have found loads of topics on this but have yet to find one that gives the answer i need.

I want to display the last insert id into a label/textbox after the INSERT function

how do i do this?

A question back at you -

Which database are you using?

|||

if the value you are using as an id is an idetity column and your database is sql server 2000 or 2005 then you can do something like this to get the last inserted value

strSQL = "";
strSQL += " Set Nocount on ";
strSQL += " Insert TableX(...) VALUES (...) ";
strSQL += " select IdentityInsert = IDENT_CURRENT( 'TableX' ) ";
strSQL += " set nocount off ";

hope this helps

|||

In sqlserver you can also do it with stored procedure:

CREATE procedure [dbo].[textbookop_insert])
as
begin
INSERT INTO [cosolu].[dbo].[cosolu_textbookop]
           ([textbookop_textbookid])
     VALUES
           (@.textbookop_textbookid)
return @.@.identity
end;
GO

You can insert the value using the following code:

 SqlConnection sqlconn = DB.CreateConn(); SqlCommand sqlcmd =new SqlCommand("textbookop_insert", sqlconn); sqlcmd.CommandType = CommandType.StoredProcedure; SqlParameter sqlpar = sqlcmd.Parameters.Add("ReturnValue", SqlDbType.Int); sqlpar.Direction = ParameterDirection.ReturnValue; sqlcmd.Parameters.AddWithValue("@.textbookop_textbookid", textbookop_textbookid); sqlconn.Open(); sqlcmd.ExecuteNonQuery();int camid = Convert.ToInt32(sqlcmd.Parameters["ReturnValue"].Value); sqlconn.Close();

Hope this helps.|||i am using an sqldatasource, dont know if that changes anything. I dont know how the syntax works when the connection is built into an object|||im using sql server 2005|||

mike7510uk:

i am using an sqldatasource, dont know if that changes anything. I dont know how the syntax works when the connection is built into an object

If the connection is built into the object, you have a TableAdapter that is connected to the table you are using. This TableAdapter contains methods that pull data from the table, like Fill() and GetData(). You can easily add another method to this TableAdapter to get the last value that was generated for the identity column of its table.

You can doubleclick on the .xsd file for the sqlDataSource, which opens the DataSet Designer. Go to the table you want to work with (lets call it myTable) and right click on any existing method in there. From this menu, choose Add Query.

This will give you a handy-dandy TableAdapter Query Configuration Wizard. In this wizard:

1. When it says "Choose a Query Type", choose "SELECT which returns a single value".

2. When it says "Specify a SQL SELECT statement, type in

SELECTIDENT_CURRENT ('myTable')FROM myTable

3. When it says "Choose a Function Name", give it a name like "GetLastIdentityValue".

Voila, you're done. So now, in your page, lets say you want to grab the ID of the last record entered and disply it in a label (Label1).

int theID = (int)myTableAdapter.GetLastIdentityValue();Label1.Text = theID.ToString();

Notice there is a cast in the first line, because the value returned by IDENT_CURRENT is of type nullable Decimal.

Hope that helps.

|||

After you do an insert you can return the key for the insert like:

INSERT INTO [dbo].[table] (name, phone)VALUES ("Denny", "555-1234")Return@.@.IDENTITY

Also be sure to read on the differences of SCOPE_IDENTITY() and @.@.IDENTITY - to be sure which one you should use to return the correct id.

|||so after i set Return @.@.IDENTITY how would that display it into a label / textbox?|||

Lee Dumond:

mike7510uk:

i am using an sqldatasource, dont know if that changes anything. I dont know how the syntax works when the connection is built into an object

If the connection is built into the object, you have a TableAdapter that is connected to the table you are using. This TableAdapter contains methods that pull data from the table, like Fill() and GetData(). You can easily add another method to this TableAdapter to get the last value that was generated for the identity column of its table.

You can doubleclick on the .xsd file for the sqlDataSource, which opens the DataSet Designer. Go to the table you want to work with (lets call it myTable) and right click on any existing method in there. From this menu, choose Add Query.

This will give you a handy-dandy TableAdapter Query Configuration Wizard. In this wizard:

1. When it says "Choose a Query Type", choose "SELECT which returns a single value".

2. When it says "Specify a SQL SELECT statement, type in

SELECTIDENT_CURRENT ('myTable')FROM myTable

3. When it says "Choose a Function Name", give it a name like "GetLastIdentityValue".

Voila, you're done. So now, in your page, lets say you want to grab the ID of the last record entered and disply it in a label (Label1).

int theID = (int)myTableAdapter.GetLastIdentityValue();Label1.Text = theID.ToString();

Notice there is a cast in the first line, because the value returned by IDENT_CURRENT is of type nullable Decimal.

Hope that helps.

where is this file? i dont have any xsd files in my folders!!

|||

Sorry, I thought you were using an ObjectDataSource. I should have read more closely. My bad. (With the OnjectDataSource, you can use a strongly-typed Dataset that contains TableAdapters.)

In the case of using an SqlDataSource, you'll can use one of the methods above to return the identity value with the insert command.

|||

Lee Dumond:

If the connection is built into the object, you have a TableAdapter that is connected to the table you are using. This TableAdapter contains methods that pull data from the table, like Fill() and GetData(). You can easily add another method to this TableAdapter to get the last value that was generated for the identity column of its table.

You can doubleclick on the .xsd file for the sqlDataSource, which opens the DataSet Designer. Go to the table you want to work with (lets call it myTable) and right click on any existing method in there. From this menu, choose Add Query.

This will give you a handy-dandy TableAdapter Query Configuration Wizard. In this wizard:

1. When it says "Choose a Query Type", choose "SELECT which returns a single value".

2. When it says "Specify a SQL SELECT statement, type in

SELECTIDENT_CURRENT ('myTable')FROM myTable

3. When it says "Choose a Function Name", give it a name like "GetLastIdentityValue".

Voila, you're done. So now, in your page, lets say you want to grab the ID of the last record entered and disply it in a label (Label1).

int theID = (int)myTableAdapter.GetLastIdentityValue();Label1.Text = theID.ToString();

Notice there is a cast in the first line, because the value returned by IDENT_CURRENT is of type nullable Decimal.

Question: doesn't this risk getting a later insert's identity value? How can I get the scope_identity value through the table adapter like this but from the same database call that actually makes the insert (less risky)? Regardless, thanks for a great solution using table adapters!

|||

Yes, is which why you should call itimmediately, right after you call the Update method. As you correctly point out, it surely doesn't make sense to use it in any other manner.

Here is an example where it could come in handy. Say, for instance, you have two tables: Orders and OrderItems. The OrderItems table is going to contain an OrderID column that is a foreign key back to the Orders table (OrderID would be a primary key column in Orders).

When you place OrderItems in your dataset, you have no idea what value to put in that foreign key OrderID column, because the current order hasn't been saved yet. You have a classic chicken-and-egg situation. What to do?

Easy... when you create the OrderItems, just put in any dummy value you want (it is going to be replaced with the correct value in a minute).

When you've added all the items you want, the current order is finished and you'll want to save it. To do that, do an Update on the OrdersTableAdapter, then immediately call the GetLastIdentityValue() method you've set up:

ordersTableAdapter.Update(myDBDataSet.Orders);int orderId = (int) ordersTableAdapter.GetLastIdentityValue();

This gives you the number to put in that foreign key column of the OrderItems table. So, now you loop through the dataset of the OrderItems, set each OrderID value of each accordingly, then update that:

foreach (myDBDataSet.OrderItemsRow rowin myDBDataSet.OrderItems){ row.OrderID = orderId;}orderItemsTableAdapter.Update(myDBDataSet.OrderItems);

Voila! All the foreign keys match, anf the integrity of the database is maintained.

|||

i did this in the end by using mysql and puttin it into the code behind.

All works good,

cheers for all help

|||

That is exactly the answer I needed... all the other projects here had been done in Deklarit, and after an update the DataRow held the new identity value... not so, apparently, with the xsd dataset in studio. This is exactly what I needed. Thanks so much!

Thomas

@@identity

I'm running the following within a For Next loop but the
@.@.identity value doesn't always show up. It only shows
for every 3rd record. Any ideas on why this might be?
Thanks,
Ben Bleser
sql = "INSERT INTO table . . . . "
conn.execute(sql)
sql = "Select @.@.Identity as Id"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
rsTmp.close
set rsTmp = nothing
Response.write RequestID & "<br><br>"
Try putting them in the same sql command string; i.e.,
sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
Also, just a performance enhancement, but if you're looping over the INSERT
statements you might consider putting your FOR...NEXT inside of the OPEN and
CLOSE statements. Opening and closing connections can add up to some
serious overhead.
"Ben Bleser" <bbleser@.airway.com> wrote in message
news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
> I'm running the following within a For Next loop but the
> @.@.identity value doesn't always show up. It only shows
> for every 3rd record. Any ideas on why this might be?
> Thanks,
> Ben Bleser
> sql = "INSERT INTO table . . . . "
> conn.execute(sql)
> sql = "Select @.@.Identity as Id"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> rsTmp.close
> set rsTmp = nothing
> Response.write RequestID & "<br><br>"
|||Another way could be to passed the values to insert to a stored procedure
and to get a OUTPUT as a return code back which would represent the
identity.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Michael C#" <howsa@.boutdat.com> schrieb im Newsbeitrag
news:OOD0dCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Try putting them in the same sql command string; i.e.,
> sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> Also, just a performance enhancement, but if you're looping over the
> INSERT statements you might consider putting your FOR...NEXT inside of the
> OPEN and CLOSE statements. Opening and closing connections can add up to
> some serious overhead.
> "Ben Bleser" <bbleser@.airway.com> wrote in message
> news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
>

@@identity

I am running an insert sp that looks like this
*****************************
@.x int,
@.x1 int,
@.x2 int,
@.NewID int
AS
BEGIN
INSERT INTO TABLE1 (x,x1,x2) VALUES (@.x,@.x1,@.x2)
SET @.New ID = (Select @.@.IDENTITY)
INSERT INTO TABLE2 (@.newID,@.x)
END
***********************
I am getting a conversion error, "Error converting data type varchar to
int.". I cannot figure out the converstion process. I have tried a ton of
different options but nothing works.
I want to run both INSERTs in the same sp.
Any ideas...>SET @.New ID = (Select @.@.IDENTITY)
Try: @.NewID = @.@.IDENTITY
If you are on SQL Server 2000, look at Scope_Identity() in BOL.
--
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
M.Smith wrote:
> I am running an insert sp that looks like this
> *****************************
> @.x int,
> @.x1 int,
> @.x2 int,
> @.NewID int
> AS
> BEGIN
> INSERT INTO TABLE1 (x,x1,x2) VALUES (@.x,@.x1,@.x2)
> SET @.New ID = (Select @.@.IDENTITY)
> INSERT INTO TABLE2 (@.newID,@.x)
> END
> ***********************
> I am getting a conversion error, "Error converting data type varchar to
> int.". I cannot figure out the converstion process. I have tried a ton of
> different options but nothing works.
> I want to run both INSERTs in the same sp.
> Any ideas...
>|||worked with SCOPE, thanks
"Lars Broberg" <lars.b@.elbe-data.nothing.se> wrote in message
news:ugIkZPFPFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Try: @.NewID = @.@.IDENTITY
> If you are on SQL Server 2000, look at Scope_Identity() in BOL.
> --
> Lars Broberg
> Elbe-Data AB
> http://www.elbe-data.se
> Remove "nothing." when replying to private e-mail!
>
> M.Smith wrote:|||Hi
Unless you want to keep @.NEWID for later use then you may want to use
SCOPE_IDENTITY() directly in the insert statement.
John
"M.Smith" <martys@.bennyhinn.org> wrote in message
news:uxQhOmFPFHA.3372@.TK2MSFTNGP10.phx.gbl...
> worked with SCOPE, thanks
> "Lars Broberg" <lars.b@.elbe-data.nothing.se> wrote in message
> news:ugIkZPFPFHA.2788@.TK2MSFTNGP09.phx.gbl...
>|||Don't forget to specify the fileds in your INSERT statement.
INTO into TheTable (Field1,Field2) values (@.Value1,@.Value2)
JN.
"M.Smith" <martys@.bennyhinn.org> a crit dans le message de news:
%23Z$GiMFPFHA.1088@.TK2MSFTNGP14.phx.gbl...
>I am running an insert sp that looks like this
> *****************************
> @.x int,
> @.x1 int,
> @.x2 int,
> @.NewID int
> AS
> BEGIN
> INSERT INTO TABLE1 (x,x1,x2) VALUES (@.x,@.x1,@.x2)
> SET @.New ID = (Select @.@.IDENTITY)
> INSERT INTO TABLE2 (@.newID,@.x)
> END
> ***********************
> I am getting a conversion error, "Error converting data type varchar to
> int.". I cannot figure out the converstion process. I have tried a ton of
> different options but nothing works.
> I want to run both INSERTs in the same sp.
> Any ideas...
>

@@identity

I'm running the following within a For Next loop but the
@.@.identity value doesn't always show up. It only shows
for every 3rd record. Any ideas on why this might be?
Thanks,
Ben Bleser
sql = "INSERT INTO table . . . . "
conn.execute(sql)
sql = "Select @.@.Identity as Id"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
rsTmp.close
set rsTmp = nothing
Response.write RequestID & "<br><br>"Try putting them in the same sql command string; i.e.,
sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
Also, just a performance enhancement, but if you're looping over the INSERT
statements you might consider putting your FOR...NEXT inside of the OPEN and
CLOSE statements. Opening and closing connections can add up to some
serious overhead.
"Ben Bleser" <bbleser@.airway.com> wrote in message
news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
> I'm running the following within a For Next loop but the
> @.@.identity value doesn't always show up. It only shows
> for every 3rd record. Any ideas on why this might be?
> Thanks,
> Ben Bleser
> sql = "INSERT INTO table . . . . "
> conn.execute(sql)
> sql = "Select @.@.Identity as Id"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> rsTmp.close
> set rsTmp = nothing
> Response.write RequestID & "<br><br>"|||Another way could be to passed the values to insert to a stored procedure
and to get a OUTPUT as a return code back which would represent the
identity.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Michael C#" <howsa@.boutdat.com> schrieb im Newsbeitrag
news:OOD0dCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Try putting them in the same sql command string; i.e.,
> sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> Also, just a performance enhancement, but if you're looping over the
> INSERT statements you might consider putting your FOR...NEXT inside of the
> OPEN and CLOSE statements. Opening and closing connections can add up to
> some serious overhead.
> "Ben Bleser" <bbleser@.airway.com> wrote in message
> news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
>> I'm running the following within a For Next loop but the
>> @.@.identity value doesn't always show up. It only shows
>> for every 3rd record. Any ideas on why this might be?
>> Thanks,
>> Ben Bleser
>> sql = "INSERT INTO table . . . . "
>> conn.execute(sql)
>> sql = "Select @.@.Identity as Id"
>> set rsTmp = conn.execute(sql)
>> RequestID = rsTmp("Id")
>> rsTmp.close
>> set rsTmp = nothing
>> Response.write RequestID & "<br><br>"
>|||Hello Ben,
Take a look at SCOPE_IDNTITY() too - in books online.
Cheers
SQLCatZ