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;