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