Sql – Multiple SQL UPDATE statements OR Single with CASE in it

sqlsql-serversql-updatetriggerstsql

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.

  1. The user can free form the priority in.
  2. There is nothing blocking them from picking the same priority as another item.

  3. 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!

Best Solution

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.

Related Question