Simplify SQL Server trigger logic

Question

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 the Images 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 increase votes 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 increase votes 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?

Answer 1

Instead of using a trigger, I would use an indexed view.

That way, the two views of the data are automatically kept synchronized by SQL Server, without writing any trigger code. You also don't have to worry about subtle race conditions and other potential concurrency issues.

Indexed view

CREATE TABLE dbo.Upvotes
(
    ImageId integer NOT NULL,
    UserId integer NOT NULL,
    Score smallint NOT NULL,

        CONSTRAINT ValidScore
        CHECK (Score IN (-1, 0, +1)),

        CONSTRAINT PK_dbo_Upvotes__ImageId_UserId
        PRIMARY KEY (ImageId, UserId)
);
GO
CREATE VIEW dbo.Images
WITH SCHEMABINDING
AS
SELECT
    U.ImageId,
    VotesUp = SUM(CASE WHEN U.Score = +1 THEN 1 ELSE 0 END),
    VotesDown = SUM(CASE WHEN U.Score = -1 THEN 1 ELSE 0 END),
    NumRows = COUNT_BIG(*)
FROM dbo.Upvotes AS U
GROUP BY 
    U.ImageId;
GO
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.Images (ImageId);

The query in the view is essentially a pivot. It is not written using PIVOT syntax, since that is not allowed in a SQL Server indexed view.

For Enterprise/Developer Edition, the optimizer will make a cost-based decision to use the view's index(es), or expand the view and access the base tables. You can force using the view indexes with a NOEXPAND hint.

In other Editions you must use the NOEXPAND hint to avoid expanding the view. There are other reasons to prefer using NOEXPAND hints with indexed views.

Example

SELECT
    I.ImageId,
    I.VotesUp,
    I.VotesDown
FROM dbo.Images AS I WITH (NOEXPAND);