title: Sql server: create a trigger after update
body: <p>I have 3 tables:</p>
  
  <ul>
  <li>Artist = {artistname, members} </li>
  <li>Musician = {msin, firstname, lastname}       </li>
  <li>Plays = {artistname, msin}</li>
  </ul>
  
  <p>Key:</p>
  
  <ul>
  <li>Artist: artistname</li>
  <li>Musician: msin</li>
  <li>Play: artistname (foreign key ref Artist), msin (foreign key ref Musician)</li>
  </ul>
  
  <p>Other Information:</p>
  
  <ul>
  <li>Artist is the Band Name, Members records the number of musicians in the band.</li>
  <li>msin is the ID number of Musician.</li>
  <li>Play records which musician play in which Band/Artist</li>
  </ul>
  
  <p>I need to create a trigger on Plays table: 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 the changes of members attribute in Musician.</p>
  
  <p>I did these in for deletion and insertion:</p>
  
  <pre><code>create trigger memberAddDel
  on Plays
  after insert, delete
  as
  if exists ( select 1 from inserted ) and not exists ( select 1 from deleted )
  begin
      update Artist
      set members = (select count(msin) 
                      from Plays p 
                      group by p.artistname 
                      having p.artistname = (select artistname from inserted))
      where artistname = (select artistname from inserted)
  end 
  else if exists ( select 1 from deleted ) and not exists ( select 1 from inserted ) 
  begin
      update Artist
      set members = (select count(msin) 
                      from Plays p 
                      group by p.artistname 
                      having p.artistname = (select artistname from deleted))
      where artistname = (select artistname from deleted)
  end
  </code></pre>
  
  <p>But I am stuck with the update part. My understanding is when updating a table, the query creates an inserted and deleted table. inserted is the new record, and deleted is the old record. I considered following situation:</p>
  
  <ul>
  <li>updates in msin in Plays only: count msin again of that artistname</li>
  <li>updates in artistname in Plays only: count msin again of both artistname (in inseserted and deleted)</li>
  <li>updates in artistname and msin : count msin again of both artistname (in inseserted and deleted)</li>
  </ul>
  
  <p>This is my try: I just update both artistnames associated members very brutally. </p>
  
  <pre><code>create trigger memberUpdate
  on Plays
  after update
  as
  if exists ( select 1 from inserted ) and exists ( select 1 from deleted )
  begin
      update Artist 
      set members = (select count(msin) 
                      from Plays p 
                      group by artistname 
                      having artistname = (select artistname from inserted)
      where artistname = (select artistname from inserted)
  
      update Artist 
      set members = (select count(msin) 
                      from Plays p 
                      group by artistname 
                      having artistname = (select artistname from deleted)
      where artistname = (select artistname from deleted)
  
  end 
  </code></pre>
  
  <p>I have Zero experience creating triggers. This is my question: Is this the correct way to handle update triggers? Is this query even correct? or any cleaner way to write this trigger?</p>