October 7, 2004 at 8:13 pm
I have a table tblMenusInRoles with following columns:
RoleID - pk
MenuID - pk
DisplaySeq - int, identity=y, identity seed=1, identity increment=1
When I insert a new record, I want to auto-increment for that pk. So if I have the following values in the column:
1,1,1
2,1,2 <- this should be 2,1,1 as the pk is different. How?
October 7, 2004 at 9:27 pm
This is somewhat a very strange Design you have!
If you want to proceed with this unorthodox method, you will have to get MAX(RoleID) and MAX(MenuID) and incrmemnt it by 1 whenever you perform an Insert on that Table and then ensure that the DisplaySeq figure corresponds with the other 2 columns.
I'm not so sure if it would work but try it.
October 8, 2004 at 9:22 am
This is a table which refers to two tables menus and roles. so menuid and roleid are pks and fks both. how would you recommend i design a join table?
October 11, 2004 at 2:08 am
Hi yaip,
I'm not sure what this design represents, my guess is that for each role you want to be able to have a different display sequence of the menu items.
If this is correct, you can use an after insert trigger, which looks for the highest DisplaySeq value for the newly added RoleID - exluding the and add 1 to determine the new value for DisplaySeq. I've added some sample code for 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
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
where RoleID = @newRoleID
and MenuID != @newMenuID
) 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
insert
into test_tab (RoleID, MenuID, DisplaySeq)
select 1, MenuID + 2, 1
from test_tab
go
select *
from test_tab
drop table test_tab
go
Hope this helps.
Cheers,
Henk
October 11, 2004 at 2:21 am
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
October 11, 2004 at 7:31 am
thanks henk
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply