Post reply

Updating column (MembershipNumber) using DELETE trigger

  • December 5, 2011 at 1:08 am

    #249211

    Hi All,

    I hope you can help. I've got a membership table that includes a column "MembershipNumber" which is the members number for a specific group. For example, I could be member #1 in Group 1, but member #9 in Group 2.

    I'm successfully inserting/updating this value, however when it comes to deleting I've come across a bit of a stumbling block. When a member leaves a group, their membership number gets "reallocated" to the next member in line. So, in my example above, if member #2 leaves from Group 2, everyone after #2 will have their membership number moved up, thus removing any gaps like 1..3,4,5,6,etc.

    Here's some code that will create and populate a cut down version of the table.

    IF OBJECT_ID('dbo.GroupMembership') IS NOT NULL

    DROP TABLE dbo.GroupMembership

    GO

    CREATE TABLE dbo.GroupMembership (

    GroupIDBIGINTNOT NULL

    ,UserIDBIGINTNOT NULL

    ,MembershipNumberINTEGER NOT NULL

    ,CreateDateDATETIMENOT NULL DEFAULT(GETDATE())

    )

    GO

    INSERT INTO GroupMembership(GroupID,UserID,MembershipNumber) VALUES(1,1,1)

    INSERT INTO GroupMembership(GroupID,UserID,MembershipNumber) VALUES(1,2,2)

    INSERT INTO GroupMembership(GroupID,UserID,MembershipNumber) VALUES(1,3,3)

    INSERT INTO GroupMembership(GroupID,UserID,MembershipNumber) VALUES(1,6,4)

    INSERT INTO GroupMembership(GroupID,UserID,MembershipNumber) VALUES(2,4,1)

    INSERT INTO GroupMembership(GroupID,UserID,MembershipNumber) VALUES(2,5,2)

    INSERT INTO GroupMembership(GroupID,UserID,MembershipNumber) VALUES(3,1,1)

    INSERT INTO GroupMembership(GroupID,UserID,MembershipNumber) VALUES(3,3,2)

    SELECT*

    FROMGroupMembership AS gm

    DROP TABLE dbo.GroupMembership

    Thanks to all in advance. As always, any help is gratefully received!

    Kevin.

  • December 5, 2011 at 2:05 am

    #1416512

    Hi Stewart,

    Thanks for the reply. I'll try it out, but from the looks of it it's exactly what I need!

    It's annoying when the solution is so simple, but I just couldn't get my head around the solution!

    Cheers,

    Kev.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply