0

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 
4
  • Have you considered an indexed view instead? Or simply a view that sums the records (and index that later if needed). Thats how I would have approached this problem in the first place. Nov 22, 2017 at 11:25
  • I am very new to the trigger and still learning. So I would stick to the dumb way of doing things. :)
    – Di Wang
    Nov 22, 2017 at 17:11
  • 1
    Why do you need to STORE a sum anywhere when you can always calculate that at exactly the point someone asks for the SUM? Is this premature optimization? An expectation that this SUM will be read a thousand times more often than it is updated? Something else? Nov 22, 2017 at 19:20
  • This is my first try. I don't know how update works in sql. I wish I do not have to use sum.
    – Di Wang
    Nov 23, 2017 at 3:19

1 Answer 1

1

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.