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