Sql-server – sql server: trigger to update in sum after update

sql servertrigger

I have 3 tables:

Artist = {artistname, startdate, members, genre}
Musician = {msin, firstname, lastname, birthdate}
Plays = {artistname, msin, share}

Key:

Artist: artistname
Musician: msin
Play: artistname (foreign key ref Artist), msin (foreign key ref Musician)

I need to create trigger on Plays: The members attribute of the Artist table should always equal the number of musicians who are members of the artist. Any insertion, deletion, update in Plays triggers changes of members attribute in Musician.

So I did following:

create trigger memberAdd
on Plays
after insert
as
if exists ( select * from Plays p )
begin
    update Artist set members = members + 1 where artistname = (select artistname from inserted)
end 
else
begin
    update Artist set members = 1 where artistname = (select artistname from inserted)
end 


create trigger memberDel
on Plays
after delete
as
begin
    update Artist set members = members - 1 where artistname = (select artistname from deleted)
end

The problem is update. I can use deleted/inserted table to trigger changes. but for updating existing the data which table should I use? How can I the find the artistname?

create trigger memberUpdate
on Plays
after update
as
begin
    update Artist 
    set members = (select sum(msin) 
                    from Plays p 
                    group by artistname 
                    having artistname = (select artistname from ????))
end

Best Answer

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

Related Question