non Identity @@Identity - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

non Identity @@Identity

Author

Topic

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-06-29 : 11:26:10

hi!

is it possible to do this:

SELECT * Table1 WHERE Table1Id=@@Identity

when Table1Id is not Identity value. it is primary key tough.
something like get the last inserted row based on PK.

Go with the flow & have fun! Else fight the flow :)

Kristen
Test

22859 Posts

Posted - 2004-06-29 : 12:44:42

I think it would be better that you store @@IDENTITY in some other, temporary, variable, but other than that it looks OK. (Probably better to use SCOPE_IDENTITY() rather than @@IDENTITY)


DECLARE @MyID int
INSERT INTO MyTable (Foo, Bar) VALUES ('Fred', 'Bill')
SELECT @MyID = SCOPE_IDENTITY()
.. any other stuff here ...
SELECT * Table1 WHERE Table1Id=@MyID

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-29 : 12:53:19

Yes SCOPE_IDENTITY() instead of @@IDENTITY!

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29208

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-29 : 12:54:23

I think the question is that Table1 does not have an identity column, and they want the next greatest value.

Why you wouldn't use IDENTITY instead I have no idea.

With that said, some people take the max,,,but there are concurrency issues with that...

Where did I put that code....be right back....

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-29 : 13:46:06

Couldn't find it...but this is prbably the best way, if that's what you need...



USE Northwind
GO

CREATE TABLE myTable99(Col1 int PRIMARY KEY, Col2 char(1), Col3ect int)
GO

CREATE TABLE myTable99NextID(NextId int, InUseIND int)
GO

-- Set the seed

INSERT INTO myTable99NextID(NextID, InUseIND) SELECT 0,0
GO

CREATE TRIGGER myTrigger99NextID ON myTable99NextID
FOR INSERT, DELETE
AS  
  BEGIN
	IF EXISTS (SELECT * FROM inserted)
	  BEGIN
		-- Nope only want to keep 1 row
	  	ROLLBACK
	  END
	IF EXISTS (SELECT * FROM deleted)
	  BEGIN
		-- Nope only want to keep 1 row
	  	ROLLBACK
	  END
  END
GO

-- Test the trigger


INSERT INTO myTable99NextID(NextID, InUseIND) SELECT 0,0
GO

SELECT * FROM myTable99NextID

SELECT @@TRANCOUNT

DELETE FROM myTable99NextID

SELECT * FROM myTable99NextID

SELECT @@TRANCOUNT

-- Looks like we could add some error messages though

GO
CREATE PROC mySproc99 @Col2 char(1), @Col3ect int
AS
DECLARE @NextId int
SET NOCOUNT ON
  BEGIN TRAN
	UPDATE myTable99NextID SET InUseIND = 1

	SELECT @NextId = NextID + 1 FROM myTable99NextID
			
	INSERT INTO myTable99(Col1, Col2, Col3ect) VALUES(@NextId, @Col2, @Col3ect)	
	
	UPDATE myTable99NextId SET NextID = @NextId, InUseIND = 0
  COMMIT TRAN
SET NOCOUNT OFF
GO


EXEC mySproc99 'A',1

SELECT * FROM myTable99

EXEC mySproc99 'B',2

SELECT * FROM myTable99
GO

DROP PROC mySproc99
DROP TABLE myTable99
DROP TRIGGER myTrigger99NextID
DROP TABLE myTable99NextID
GO

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-06-29 : 14:02:28

Hey, great trigger Brett!


	IF EXISTS (SELECT * FROM inserted)
	  BEGIN
		-- Nope only want to keep 1 row
	  	ROLLBACK
	  END
	IF EXISTS (SELECT * FROM deleted)
	  BEGIN
		-- Nope only want to keep 1 row
	  	ROLLBACK
	  END

I'm still trying to work out how that one works. Is it to reduce the DBA's workload?!

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-29 : 15:45:48

quote:
Originally posted by Kristen

Hey, great trigger Brett!

IF EXISTS (SELECT * FROM inserted)
BEGIN
-- Nope only want to keep 1 row
ROLLBACK
END
IF EXISTS (SELECT * FROM deleted)
BEGIN
-- Nope only want to keep 1 row
ROLLBACK
END

I'm still trying to work out how that one works. Is it to reduce the DBA's workload?!

Kristen

It's to prevent an insert or a delete...run the code and you'll see...I seed the controlling table with 1 row, and then only allow 1...it keeps the Current seed. When the In Use column...it get's locked preventing any possibility of 2 rows truing to insert the same key...

But that's only if the all use the sproc...

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-06-30 : 05:46:55

I did run the code, but I still couldn't figure it (not enuf time allocated to Study Time!). I thought that an UPDATE created a row in both INSERTED and DELETED - thus your code would disallow UPDATEs too ... but I've musta got that wrong ...

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-06-30 : 06:27:40

maybe i asked the question wrongly... or i'm unable to "translate" suggestions into what i need.

say you have a table which has a Table1ID varchar(3) Primary key.
Table1ID is not in any particular order... it could be i.e.:
'abc'
'def'
'ghi'

or

'def'
'abc'
'ghi'

my point is that the max value of PK is not important.

is it possible to get the last inserted row similar to that of Scope_identity() or @@identity does for autonumber columns.
tmep tables, and trigger are not really an option. if it can't be done in a simple where clause, i'll solve it on the user end.

Go with the flow & have fun! Else fight the flow :)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-06-30 : 06:33:38

unless you add a 'last-modified/inserted' column to your table....your problem is doomed to have no solution......

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-06-30 : 07:03:06

yeah that's what i thought.... but it doesn't hurt to ask :)

thanx everyone

Go with the flow & have fun! Else fight the flow :)

Kristen
Test

22859 Posts

Posted - 2004-06-30 : 10:09:15

Only way I can think of is with an an After Insert Trigger which "remembered" (in another table) the PK of the last row added,

When you say "Like Identity" ... that would only be available immediately after the INSERT, at which point you would know the PK of the record you were just inserting (nothing else "external" is assigning the PK is it?), so can't you use that?

Perhaps I need to better understand the problem you are trying to solve and see if I/we can come up with a suggestion to that.

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-06-30 : 11:29:54

Kristen:
i do use it in the way you suggested :) if the PK is varchar or something like that...
i insert data with ADO.NET (does't everyone?? :))

simple illustration:
say you insert row which has TableId uniqueidentifier PK. you do System.Guid.NewGuid() for TableId.
press a button which inserts it into db. push another button which gets the last inserted id and displays it.
of course you can read it out of the dataset, but for identity you could say Select SCOPE_IDENTITY() and it would get it. for uniqueidentifier or varchar you can not.

i was just wondering if something similar exists for i.e. uniqueidentifier, or say if you have a default random function for PK....

it's not a problem i have, i was just wondering if it exists. so i searched the net and BOL, and now i posted the question here. there is no need for overthinking on this one for my sake... :)))

Go with the flow & have fun! Else fight the flow :)

Kristen
Test

22859 Posts

Posted - 2004-06-30 : 15:45:03

Well, my answer would be that we use SProcs for ALL access to the database. No dynamic stuff from ASP et al.

So normally I would set a DEFAULT on a [uniqueidentifier] column to be "NewID()" and that would ensure that that column got a GUID if it was left empty in an INSERT.

However, if I needed to know the value of the allocated GUID (like @@IDENTITY, as you say) then I would do:


DECLARE @MyGUID uniqueidentifier
...
SELECT @MyGUID = NewID()
...
INSERT INTO MyTable(MyColumn1, MyColumn2, ..., MyGUIDColumn, ...)
SELECT 'XXX', 'YYY', ..., @MyGUID, ...

and then I've got the value of @MyGUID in a variable to return as either an OUTPUT Parameter, or a ResultSet

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-07-01 : 04:16:51

my thougths exactly. :)
i do use sprocs for all data access.
but you did answer my question so thanx.

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources