Simulating an identity column within an insert trigger

Posted by William Jens on Stack Overflow See other posts from Stack Overflow or by William Jens
Published on 2010-05-28T17:10:35Z Indexed on 2010/05/28 17:12 UTC
Read the original article Hit count: 234

Filed under:
|
|

I have a table for logging that needs a log ID but I can't use an identity column because the log ID is part of a combo key.

create table StuffLogs
{
   StuffID int
   LogID int
   Note varchar(255)
}

There is a combo key for StuffID & LogID.

I want to build an insert trigger that computes the next LogID when inserting log records. I can do it for one record at a time (see below to see how LogID is computed), but that's not really effective, and I'm hoping there's a way to do this without cursors.

select @NextLogID = isnull(max(LogID),0)+1 
from StuffLogs where StuffID = (select StuffID from inserted)

The net result should allow me to insert any number of records into StuffLogs with the LogID column auto computed.

StuffID  LogID  Note
123      1      foo
123      2      bar
456      1      boo
789      1      hoo

Inserting another record using StuffID: 123, Note: bop will result in the following record:

StuffID  LogID  Note
123      3      bop

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server-2005