SQL Instead of trigger is not fireing sometimes?

Table Event has one instead of trigger, whos purpose is to generate primary key EventId as Max+1, rest of the columns are populated from inserted.

EventId is not Identity, and we can't make it identity as there is lots of depedency there, Trigger logic:

SELECT TOP 1 @ID = Event.EventID FROM Event
IF (@ID IS NULL)
BEGIN
  SET @ID=1
END
ELSE
BEGIN
  SELECT @ID = MAX(Event.EventID) FROM Event
  SET @ID=@ID+1
END
--Then just a insert statment with this id as EventId and rest of the columns from inserted table

Now sometimes when i tries to ins开发者_JS百科ert into this table, it still says can't insert duplicate in eventId, Not sure why this is happning...

Looks like trigger is not fireing in some case? Why

You're probably assuming that there's a single row in the inserted pseudo table, and failing when a multi-row insert occurs.

You want something like:

;with maxID as (
    select MAX(EventID) as EventID from Event
), nrows as (
    select
        ROW_NUMBER() OVER (ORDER BY newid()) +
            COALESCE((select EventID from maxID),0) as EventID,
        /* columns from inserted table */
    from inserted
)
insert into Event (EventID,/* other columns */)
select EventID,/* other columns */
from nrows

You need to use some kind of locking to prevent duplicate IDs from being generated. Take a look at this thread of a similar problem for ideas on how to tackle this.