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