title: SQL Trigger for Chinook database: condition when sum is null
body: <p>I am brushing up on my SQL and using the Chinook database for practice.</p>
  
  <p>the data model can be found here: <a href="https://chinookdatabase.codeplex.com/wikipage?title=Chinook_Schema&amp;referringTitle=Documentation" rel="nofollow">https://chinookdatabase.codeplex.com/wikipage?title=Chinook_Schema&amp;referringTitle=Documentation</a></p>
  
  <p>The goal is to write a trigger so that the Total in the Invoice table is updated when I insert or delete invoicelines.</p>
  
  <pre><code>CREATE TRIGGER UpdateTotal ON InvoiceLine
  AFTER INSERT, DELETE
  AS
      UPDATE Invoice
      SET Total = (
          SELECT sum(LineSum) AS InvoiceTotal
          FROM (
              SELECT InvoiceId, (UnitPrice * Quantity) AS LineSum 
              FROM InvoiceLine 
          ) AS WithLineSum
          GROUP BY InvoiceId
          HAVING WithLineSum.InvoiceId = Invoice.InvoiceId
      )
  </code></pre>
  
  <p>and this works great when I insert and delete invoiceline records, except for when I delete the very last invoiceline for an invoice. When I do that, i get the error:</p>
  
  <p>Cannot insert the value NULL into column 'Total', table 'Chinook.dbo.Invoice'; column does not allow nulls. 
  UPDATE fails.</p>
  
  <p>so basically I need to set it to zero if sum(LineSum) is null</p>
  
  <p>I am struggling to figure out how to structure the conditional, can anyone please help?</p>