Simulating an identity column within an insert trigger

Question

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

Answer 1

Select Row_Number() Over( Order By LogId ) + MaxValue.LogId + 1
From inserted
    Cross Join ( Select Max(LogId)  As Id From StuffLogs ) As MaxValue

You would need to thoroughly test this and ensure that if two connections were inserting into the table at the same time that you do not get collisions on LogId.

Answer 2

Unless there is a rigid business reason that requires each LogID to be a sequence starting from 1 for each distinct StuffID, then just use an identity. With an identity, you'll still be able to order rows properly with StuffID+LogID, but you'll not have the insert issues of trying to do it manually (concurrency, deadlocks, locking/blocking, slow inserts, etc.).

Answer 3

Make sure the LogId has a default value of NULL, so that it need not be supplied during insert statements, like it was an identity column.

CREATE TRIGGER Insert ON dbo.StuffLogs
INSTEAD OF INSERT
AS
UPDATE #Inserted SET LogId = select max(LogId)+1 from StuffLogs where StuffId=[INSERTED].StuffId