Simplify SQL Server trigger logic

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

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);

StackExchange Database Administrators Q#165714, answer score: 7