Simplify SQL Server trigger logic
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);