@@identity global variable - 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

General SQL Server Forums

New to SQL Server Programming

@@identity global variable

Author

Topic

dunleav1
Starting Member

2 Posts

Posted - 2009-09-14 : 15:43:24

I have inherited code that I am supporting.
This piece of code is a update trigger on table1.
The trigger does an insert on table2.
insert into table2 default values
(table2 is a 1 column table defined as numeric)
The trigger then references @@identity after the insert into table2.

Is it safe to call the global variable @@identity on a numeric datatype and not a identity datatype?

trigger has another insert into a table called x.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-14 : 16:24:00

@@identity ONLY works on identity columns. identity columns can be of datatype numeric as long as the scale is 0.
So unless your column is an IDENTITY column (which is not a datatype its a property) then @@identity won't work.

However, You shouldn't use @@identity or scope_identity() in a trigger because that would assume you are only inserting one row per statement. For a multi row operation @@identity and scope_identity() will only return the last value.

EDIT:
post the trigger code

Be One with the Optimizer
TG

dunleav1
Starting Member

2 Posts

Posted - 2009-09-15 : 09:49:30

I was incorrect table2 is defined as an identity datatype. So I'm ok there.

Here's the ddl for LIUpdIdSeq table:
CREATE TABLE [dbo].[LIUpdIdSeq](
[seqId] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
PRIMARY KEY CLUSTERED
(
[seqId] ASC
)
)

But I'm a little bit concerned about your statement. Can you explain what you mean by this?

"However, You shouldn't use @@identity or scope_identity() in a trigger because that would assume you are only inserting one row per statement. For a multi row operation @@identity and scope_identity() will only return the last value."

Here is the trigger code:
CREATE TRIGGER tr_UpdateLineItem on LineItem
-- Here's the operation type
FOR UPDATE

AS
--local variables
declare @countofrows int
declare @seqId numeric(18)
declare @sourceId numeric(18)
declare @transSyncIdCode varchar(40)
declare @transSyncSeqNum numeric(18)

-- save off the row count
select @countofrows = @@rowcount

if not update(transStatusTypeId)
return

if not update(updateId)
begin
if @countofrows = 0 return

-- get values out of inserted
select @transSyncIdCode = transSyncIdCode from inserted
select @sourceId = sourceId from inserted
select @transSyncSeqNum = transSyncSeqNum from inserted

-- nust generate a new sequence value
INSERT INTO LIUpdIdSeq default values

If @@error <> 0
begin

--return
raiserror('tr_UpdateLineItem trigger failed Next Sequence insert failed for LIUpdIdSeq table with errorcode=%d',16,1,@@error)

end

-- get next sequence value
-- IS THIS A CONCERN?
select @seqId = @@Identity

--Sets UpdateId to previously generated identity make sure the where clause is indexed
update LineItem
set updateId = @seqId
where LineItem.transSyncIdCode = @transSyncIdCode
and LineItem.sourceId = @sourceId
and LineItem.transSyncSeqNum = @transSyncSeqNum

If @@error <> 0
begin
raiserror('tr_UpdateLineItem trigger failed update failed for Lineitem table where TransSyncIdCode=%d and sourceId=%d with error code=%d',16,1,@transSyncIdCode,@sourceId,@@error)
end

-- delete the sequence make sure that the seqId is indexed
delete from LIUpdIdSeq where seqId=@seqId
end;

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-15 : 11:39:01

>>Can you explain what you mean by this?

Sure. Your design expects that only one [Lineitem] row will ever be updated at a time. I say that because you are assigning values from inserted to variables. Obviously a variable can only hold one value at a time. However, and this is generally considered best practice for triggers, you should not assume that a table will only have one row affected. In your case, even if your application won't allow it, a DBA for some reason may determine that an UPDATE needs to applied to the [lineitem] table which affects more than one row. So [inserted] may contain say 10 rows. Well your local variables will only have the values of one of those 10 rows so when you update [updateid] only one of the 10 rows will be updated. Of course even if you remove the local variables and just JOINed lineitem directly to inserted, which is how it should be, you would update all 10 rows with the same seqid. Obviously that logic would need to change as well.

So my question is why is there a seperate table with an identity column as opposed to just making [lineitem].[updateid] an identity column?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources