Re: Trigger function failure

I have a trigger function problem that threatens to cause my early demise.  Please someone help an absolute novice.

I have two tables, invoice and invline, with the usual functions.

Table invoice has a column for the total of debit lines, and a column for the total of credit (payment) lines.  The function is triggered by any change in inv.gross

The ERROR occurs if there are no invline found by either summing part, where we set dr_total or cr_total.

If I change "sum" to "count" it works as expected, returning 0.00

--THE FUNCTION

UPDATE invoice SET 

invoice.cr_total = (

SELECT 

sum(invline.gross)

FROM invline

WHERE invline.type > 4 AND invline.invoice_id = 200003

)

,

        invoice.dr_total = (

SELECT

sum(invline.gross)

FROM invline

WHERE invline.type <5 AND invline.invoice_id = 200003

)

WHERE invoice.id = 200003;

--TEST

select invoice.dr_total, invoice.cr_total from invoice where invoice.id=200003

---------------------------------------------------------------------------------------------

ERROR:  null value in column "po_cr_total" violates not-null constraint

Anyone?

Mike