views:

174

answers:

5

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!

A: 

How about this:

 UPDATE Stories SET Priority = CASE 
     WHEN Priority > @OldLocation THEN Priority-1
     WHEN Priority >= @NewLocation AND StoryID <> @StoryID THEN Priority+1
     WHEN Priority >= @NewLocation AND @Priority < @OldLocation And StoryID <> StoryID THEN Priority +1
END
GO
JonH
A: 

There shouldn't be any (noticeable) performance difference, in any case you will be issuing a single update statement to the database.

IronGoofy
+1  A: 

Make sure you rollback and raise an error (severity 16) if @@ROWCOUNT > 1. Your trigger as currently written would cause a great deal of data corruption of the user attempted to insert, update or delete multiple rows at once.

That said, IronGoofy is right: you will only be touching the table once, regardless if the condition. So, breaking it out into multiple statements makes the code easier to read/more maintainable.

If you were to allow multiple rows to be updated at once, you would need to change this. The logic may be daunting!

Peter
+1  A: 

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.

HLGEM
A: 

I decided to ditch this idea, and I leave it up to the UI to update the priority. I've stopped allowing user input.

I'm not sure which of these answers are correct, so I'm going to mark this as correct and let the community figure it out with trial and error. :)

Altonymous