sql server: trigger to update in sum after update
I'd like suggest another approach.

Instead of adding or deleting one record, you can use the same trigger for both insert and delete, -(Do you really need to count records after update?)-, and then you can count all records and set members field.

Just keep in mind that you can insert or delete more than one record, you should consider an INNER JOIN with inserted and deleted tables.




CREATE TABLE Artists (artistName nvarchar(200), members int);
CREATE TABLE Plays   (artistName nvarchar(200), share int);

INSERT INTO Artists VALUES ('Eric', 0), ('Luca', 0);
GO






CREATE TRIGGER trgPlays 
ON Plays
AFTER insert, delete, update
AS

    IF EXISTS (SELECT 1 FROM inserted)
    BEGIN
        UPDATE     Art
        SET        members = (SELECT count(*) FROM Plays WHERE artistName = i.artistName)
        FROM       inserted i
        INNER JOIN Artists Art
        ON         Art.artistName = i.artistName
    END
    ELSE IF EXISTS (SELECT 1 FROM deleted)
    BEGIN
        UPDATE     Art
        SET        members = (SELECT count(*) FROM Plays WHERE artistName = d.artistName)
        FROM       deleted d
        INNER JOIN Artists Art
        ON         Art.artistName = d.artistName
    END
GO






SELECT artistName, members
FROM   Artists
GO



artistName | members
:--------- | ------:
Eric       |       0
Luca       |       0






INSERT INTO Plays VALUES ('Eric', 1), ('Eric', 2), ('Luca', 1);
INSERT INTO Plays VALUES ('Eric', 3);
DELETE FROM Plays WHERE artistName = 'Eric' AND share=2;
GO






SELECT artistName, members
FROM   Artists
GO



artistName | members
:--------- | ------:
Eric       |       2
Luca       |       1



dbfiddle here