-- -- trig_ec_cert_amount_remains/0 -- create or replace function trig_ec_cert_amount_remains( ) returns trigger as $$ DECLARE bal_amount_used numeric; original_amount numeric; BEGIN select amount into original_amount from ec_gift_certificates where gift_certificate_id= NEW.certificate_id for update; select coalesce(sum(amount_used), 0) - coalesce(sum(amount_reinstated), 0) into bal_amount_used from ec_gift_certificate_usage where gift_certificate_id= NEW.gift_certificate_id; UPDATE ec_gift_certificates SET amount_remaining_p = case when amount > bal_amount_used then 't' else 'f' end WHERE gift_certificate_id = gift_certificate_rec.gift_certificate_id; return new; END; $$ language plpgsql;