sql server - SQL Trigger for Chinook database: condition when sum is null -

i brushing on sql , using chinook database practice.

the data model can found here: https://chinookdatabase.codeplex.com/wikipage?title=chinook_schema&referringtitle=documentation

the goal write trigger total in invoice table updated when insert or delete invoicelines.

create trigger updatetotal on invoiceline after insert, delete     update invoice     set total = (         select sum(linesum) invoicetotal         (             select invoiceid, (unitprice * quantity) linesum              invoiceline          ) withlinesum         group invoiceid         having withlinesum.invoiceid = invoice.invoiceid     ) 

and works great when insert , delete invoiceline records, except when delete last invoiceline invoice. when that, error:

cannot insert value null column 'total', table 'chinook.dbo.invoice'; column not allow nulls. update fails.

so need set 0 if sum(linesum) null

i struggling figure out how structure conditional, can please help?

your approach inefficient updating every invoice every time instead of affected operation. need leverage special inserted , deleted tables within trigger.

create trigger updatetotal on invoiceline after insert, delete     set nocount on;      update inv     set total = total + (i.unitprice * i.quantity)         inserted             inner join invoice inv                 on i.invoiceid = inv.invoiceid;      update inv     set total = total - (d.unitprice * d.quantity)         deleted d             inner join invoice inv                 on d.invoiceid = inv.invoiceid;