Hi yaip,

I discovered a minor flaw in the previous code when new records contain sequence numbers that are greater than the existsing numbers (if a gap in the DisplaySeq values is considered a gap).

The following code fixes this:

create table test_tab

(RoleID int

,MenuID int

,DisplaySeq int

,primary key (RoleID, MenuID)

)

go

create trigger test_trg

on test_tab

after insert

as

begin

-- first we need to update all new DisplaySeq values to 0

-- this prevents gaps in the column value when a record with

-- a higher sequence number is added

update test_tab

set DisplaySeq = 0

from inserted i

where test_tab.RoleID = i.RoleID

and test_tab.MenuId = i.MenuID

declare c_new cursor local fast_forward

for

select RoleID, MenuID

from inserted

declare @newRoleID int, @newMenuID int

open c_new

fetch next from c_new

into @newRoleID, @newMenuID

while @@fetch_status = 0

begin

update test_tab

set DisplaySeq = isnull(t.new_val, 0) + 1

from (select max(DisplaySeq) new_val

from test_tab tt

where RoleID = @newRoleID

) t

where RoleID = @newRoleID

and MenuID = @newMenuID

fetch next from c_new

into @newRoleID, @newMenuID

end

close c_new

deallocate c_new

end

go

-- let's do some inserts

insert

into test_tab (RoleID, MenuID, DisplaySeq)

values (1, 1, 1)

insert

into test_tab (RoleID, MenuID, DisplaySeq)

values (1, 2, 1)

-- insert multiple records with higher sequence numbers

insert

into test_tab (RoleID, MenuID, DisplaySeq)

select 1, MenuID + 2, 10

from test_tab

go

select *

from test_tab

drop table test_tab

go

Cheers,

Henk