March 24, 2003 at 12:52 pm
Greetings,
I was pulling my hair out for a while trying to figure out why I simply could not get a value back from a stored proc.
Just as an example
table test
intID identity(1,1) not null
vchrdesc varchar(10)
create proc foo
@identnum int OUT
set nocount on
insert into test (vchrdesc) values ('testing')
Select @@identity as @identnum
set nocount off
I create the table, I run the proc, I would get back the proper @@identity info.
HOWEVER, if I add a triggers to the table for INSERT, I will get back the @@identity of the INSERT table of the trigger and not the TEST table.
I have tried adding SET NOCOUNT ON in the trigger as well but it doesn't help.
Any thoughts on how I can get the proper value?
Regards,
Christopher Klein
p.s.
mssql2k, sp3
March 24, 2003 at 1:04 pm
The value from the test table is different than from the inserted table?
March 24, 2003 at 1:25 pm
If you have SQL2K, you can use scope_identity. Other option would be to not us an identity in one of the tables, perhaps using uniqueidentifier with newid() instead.
Andy
March 24, 2003 at 1:49 pm
you could also execute a select top 1 intid from test ORDER BY intid DESC
OR SELECT MAX(intID) FROM Test in your stored procedure
March 24, 2003 at 2:30 pm
As the others have said, the problam lies in th fact that @@identity returns the latest created identity-value in the current session. As Andy suggested you can use scope_identity instead as it is limited to the current scope (it will not 'catch' the identity created by the trigger). There is also the function ident_current that returns that last identity-value created for a specific table, but since it is not limited to any session you might actually get someone elses identity-value with this.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 24, 2003 at 4:39 pm
Worst case scenario is to capture the identity in the trigger and spit it back out in a recordset. Not great - try scope_identity first.
Guarddata-
March 28, 2003 at 9:01 am
Very Easy:
create proc foo
set nocount on
insert into test (vchrdesc) values ('testing')
SET NOCOUNT OFF
RETURN @@IDENTITY
March 29, 2003 at 2:48 am
chance, the problem here was that a trigger on table inserted to was resulting in @@identity returning an unexpected value, i.e. one from a second insert done by the trigger. Your solution would not solve this.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 31, 2003 at 8:13 am
I would suggest that you use newid() to get a uniqueidentifier type and use that instead of an identity column. It makes life so much easier - especially if you ever replicate data.
I also do not use triggers unless tortured by the Spanish Inquisition. You can almost 100% of the time write the code so that you do not need triggers.
I restrict all application access through Stored Procs and only grant execute on the SP to the users accounts doing the access for the application. This way they can't do a SELECT, UPDATE, INSERT, or DELETE except through a stored proc and the audit data update is part of the SELECT, UPDATE, INSERT, or DELETE Stored Proc. I also get to audit SELECTs this way (which is actually a requirement for one app)
This seems kinda draconian, but I think that it is the best way to do it and even if you are doing a small project, if you set up one application this way, you'll never want to go back to triggers (IMHO).
March 31, 2003 at 9:27 am
While I agree with mgeiser in the use of stored procedures - and personally prefer to use very few triggers, I find GUIDs to be difficult in our implementation because of the space required and other similar issues. Scope_Identity() solves all the problems that I face.
Guarddata-
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy