Simulate an identity column inside an insert trigger

Question

I have a logging table that needs a log id, but I cannot use the id column because the log id is part of the combined key.

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

There is a combination key for StuffID and LogID .

I want to create an insert trigger that calculates the next LogID when inserting log entries. I can do this for one record at a time (see below to find out how the LogID calculated), but it is not very efficient, and I hope there is a way to do this without cursors.

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

The end result should allow me to insert any number of records into StuffLogs with automatic calculation of the LogID column.

 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

If there is no tough business reason that requires each login to be a sequence starting at 1 for each individual StuffID element, just use the identifier. With a personality, you can still correctly arrange the lines using StuffID + LogID, but you will not have insertion problems when trying to do this manually (concurrency, deadlocks, lock / lock, slow inserts, etc.).).

Answer 2

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

You will need to carefully test this and make sure that if two connections are inserted into the table at the same time that you are not getting collisions in LogId.

Answer 3

Ensure that the LogId value is NULL by default, so it does not need to be specified during insert instructions, for example, 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