I've built a trigger where I am trying to keep priorities in order and not allow duplicate priority values. There are some things to consider.
- The user can free form the priority in.
- There is nothing blocking them from picking the same priority as another item.
When a value is entered equal to another value the newly prioritized item should take precedence in the priority and the other should be incremented.
CREATE TRIGGER dbo.trg_Priority ON dbo.Stories AFTER INSERT,UPDATE,DELETE AS BEGIN SET NOCOUNT ON; -- Insert statements for trigger here DECLARE @StoryId INT DECLARE @OldLocation INT DECLARE @NewLocation INT SELECT @NewLocation = Priority, @StoryId = StoryId FROM INSERTED SELECT @OldLocation = Priority FROM DELETED IF @NewLocation = @OldLocation RETURN; IF @NewLocation IS NULL BEGIN UPDATE Stories SET Priority = Priority - 1 WHERE Priority > @OldLocation END IF @NewLocation > @OldLocation BEGIN UPDATE Stories SET Priority = Priority + 1 WHERE Priority >= @NewLocation AND StoryId <> @StoryId END IF @NewLocation < @OldLocation BEGIN UPDATE Stories SET Priority = Priority + 1 WHERE Priority >= @NewLocation AND Priority < @OldLocation AND StoryId <> @StoryId END END GO
I haven't tested this trigger a whole lot so if there's areas of concern feel free to speak up. What I ultimately want to know is if I should try and convert this to a single update with a case statement. (If that's even possible.)
If it would be more performant to make this a single UPDATE statement I could really use a hand figuring it out!