I have an Images
table where that table has these basic columns —
ImageId (int)
VotesUp (int)
VotesDown (int)
Each image can get upvotes (up , down , retract vote)
The upvotes table has :
ImageId (int)
UserId (int)
Score (int ) [-1,0,+1]
So if —
A user inserts +1 score then ( if not exists) I add a new row to
upvotes
table & update the value in theImages
table ( for quick fetching)A user changes its score from +1 to 0 then decrease
votes up
- A user changes its score from +1 to -1 then decrease
votes up
and increasevotes down
- A user changes its score from 0 to +1 then increase
votes up
A user changes its score from 0 to -1 then increase
votes down
A user changes its score from -1 to 0 then decrease
votes down
- A user changes its score from -1 to +1 then decrease
votes down
and increasevotes up
It's a pretty simple logic.
Here is the trigger on the upvotes
table :
alter TRIGGER [dbo].[UpvotesChanged]
ON [dbo].[Upvotes]
FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS( SELECT 1 FROM DELETED ) --update
BEGIN
UPDATE imgs
SET VotesUp = CASE
WHEN deleted.Score = 1 AND INSERTED.score =0 THEN ISNULL(VotesUp, 0) -1
WHEN deleted.Score = 1 AND INSERTED.score =-1 THEN ISNULL(VotesUp, 0) -1
WHEN deleted.Score = 0 AND INSERTED.score =1 THEN ISNULL(VotesUp, 0) +1
WHEN deleted.Score = -1 AND INSERTED.score =1 THEN ISNULL(VotesUp, 0) +1
ELSE ISNULL(VotesUp, 0)
END
,
VotesDown = CASE
WHEN deleted.Score = 0 AND INSERTED.score =-1 THEN ISNULL(VotesDown, 0) +1
WHEN deleted.Score = 1 AND INSERTED.score =-1 THEN ISNULL(VotesDown, 0) +1
WHEN deleted.Score = -1 AND INSERTED.score =1 THEN ISNULL(VotesDown, 0) -1
WHEN deleted.Score = -1 AND INSERTED.score =0 THEN ISNULL(VotesDown, 0) -1
ELSE ISNULL(VotesDown, 0)
END
FROM Images imgs
JOIN DELETED
ON imgs.ImageId = deleted.ImageId
JOIN INSERTED ON imgs.ImageId = INSERTED.ImageId
END
ELSE
--insert
BEGIN
UPDATE imgs
SET VotesUp = CASE
WHEN INSERTED.Score = 1
THEN ISNULL(VotesUp, 0) + 1
ELSE VotesUp
END,
VotesDown = CASE
WHEN INSERTED.Score = -1
THEN ISNULL(VotesDown, 0) + 1
ELSE VotesDown
END
FROM Images imgs
JOIN INSERTED
ON imgs.ImageId = INSERTED.ImageId
END
END
It's working as expected.
So where is the problem ?
Question :
I think this trigger can be simplified without all these if's/cases . I just can't see the simplification.
Is it possible to simplify this trigger?