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!
You need to rewrite the trigger. It assumes only one records will ever be inserted/updated or delted at atime. You cannot write a trigger with that assumption, triggers operate on batches of data not row-byrow. You need to join to inserted and deleted in your updates. So yes, I would try to write the update with a case statement.
And why is this a deleted trigger? There won;t be arecord to update if it was deleted.