Trigger function failure

From: Michael Rowan <michael(dot)rowan3(at)gmail(dot)com>
To: pgsql-novice novice <pgsql-novice(at)postgresql(dot)org>
Subject: Trigger function failure
Date: 2016-03-07 06:43:13
Message-ID: CAL04Mk=f_B1+X8GjbSLPz+P0N0ANbGygbRLxCY1KXrpVGEiPJA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Fábio Moreira 2016-03-07 06:50:30 Re: Trigger function failure
Previous Message David G. Johnston 2016-03-03 23:20:13 Re: Divide table raw into chunks