Dear all,
I have issue with the red portion function below;
This IF last_id IS NULL THEN suppose to test if last_id is NULL, that is the
select query did not found it
then execute the rest of the red sql but it always fail to insert the
NEW.amount into amount , every other things fine.
Kindly help out.
CREATE OR REPLACE FUNCTION invoice_trigger_func() RETURNS trigger AS
$$
DECLARE
last_id integer;
current_balance_id integer;
oldbalance numeric(10,2);
newbalance numeric(10,2);
BEGIN
SELECT lastbal_id INTO last_id FROM patient
WHERE patient_id = NEW.patient_id;
IF last_id IS NULL THEN
INSERT INTO balance (invoice_id, patient_id, amount)
VALUES (NEW.invoice_id, NEW.patient_id, NEW.amount);
UPDATE patient SET lastbal_id = (SELECT currval('balance_balance_id_seq'))
WHERE patient_id = NEW.patient_id;
ELSE
SELECT amount INTO oldbalance FROM balance
WHERE balance_id = last_id;
INSERT INTO balance (invoice_id, patient_id, amount)
VALUES (NEW.invoice_id, NEW.patient_id, oldbalance + NEW.amount);
UPDATE patient SET lastbal_id = (SELECT currval('balance_balance_id_seq'))
WHERE patient_id = NEW.patient_id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Thanks,
Sunday Olutayo
