Re: Procedure/trigger not working after upgrade from 9.5 to 11.7

Hi,

Is this something new that came after 9.5, because it DID work on 9.5.. So
far oly noticed on inserts.

Thanks,
erika

On Wed, 25 Mar 2020 at 19:40, jim schmidt <txherper(at)gmail(dot)com> wrote:

> Old is not assigned on inserts.
>
> Is this occurring on updates?
>
> On Wed, Mar 25, 2020, 1:22 PM Erika Knihti-Van Driessche <
> erika(dot)knihti(at)gmail(dot)com> wrote:
>
>> Hi all,
>>
>> Hope I'm in the correct mail group.. I don't have developer background,
>> and after upgrade from 9.5 to 11.7 (RHEL7) the users complain about a
>> trigger not working. Here's the error I find in logs:
>>
>> ERROR: record "old" is not assigned yet
>> DETAIL: The tuple structure of a not-yet-assigned record is
>> indeterminate.
>> CONTEXT: SQL statement "SELECT OLD.Ffa_Id Is Null"
>> PL/pgSQL function pg_fct_bus_fax_finance_employer() line 20 at IF
>>
>> Here's part of the procedure (where it already gives the error)
>>
>> Declare
>>
>>
>>
>> Tra_Id Worker.Tra_Id%Type;
>>
>>
>>
>> Tra_DateEnregDomTVA Worker.Tra_DateEnregDomTVA%Type;
>>
>>
>>
>> Exercice Alloc_compl_vac_worker.Acv_Exercice%Type;
>>
>>
>>
>> CodeLangue Worker.Tra_Langue%Type;
>>
>>
>>
>> r RECORD;
>>
>>
>>
>> r2 RECORD;
>>
>>
>>
>> r3 RECORD;
>>
>>
>>
>> EREC fax_finance_employer%ROWTYPE;
>>
>>
>>
>> Begin
>>
>>
>>
>> SET search_path = fs;
>>
>>
>>
>>
>>
>>
>>
>> If TG_OP = 'INSERT' THEN
>>
>>
>>
>> OLD = EREC;
>>
>>
>>
>> END IF;
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> -- if the number is not specified, then it is automatically provided by
>> the system
>>
>> if OLD.Ffa_Id Is Null Then -- Creation
>>
>>
>>
>> if NEW.Ffa_NoPayement Is Null Then
>>
>>
>>
>> Exercice := EXTRACT(YEAR FROM NEW.FFA_DATEGENERATION);
>>
>>
>>
>> if NEW.DAN_ID is not null then
>>
>>
>>
>> NEW.Ffa_NoPayement := I_FAX_FINANCE_NumeroNext(Exercice,
>> 'RJA');
>>
>>
>> end if;
>>
>>
>>
>> if NEW.DDT_ID is not null then
>>
>>
>>
>> NEW.Ffa_NoPayement := I_FAX_FINANCE_NumeroNext(Exercice,
>> 'RDT');
>>
>>
>> end if;
>>
>>
>>
>> if NEW.CFS_ID is not null then
>>
>>
>>
>> NEW.Ffa_NoPayement := I_FAX_FINANCE_NumeroNext(Exercice,
>> 'RFS');
>>
>>
>> end if;
>>
>>
>>
>> if NEW.NDA_ID is not null then
>>
>>
>>
>> NEW.Ffa_NoPayement := I_FAX_FINANCE_NumeroNext(Exercice,
>> 'AST');
>>
>>
>> end if;
>>
>>
>>
>> If NEW.Ffa_NoPayement is null Then
>>
>>
>>
>> RAISE EXCEPTION '-20068|There is no more number
>> available...';
>>
>>
>> End If;
>>
>>
>>
>> End If;
>>
>>
>>
>> End IF;
>>
>>
>>
>>
>> I know it's just a part, but I was hoping someone could immediately say
>> AHAA :-)
>>
>> For the upgrade, I used
>> pg_dumpall -g (first only globals)
>> psql (import globals)
>> pg_dumpall (dump all)
>> psql (import all)
>>
>> No errors received during dump/import.
>>
>> Thanks for your help & support!
>> Erika
>>
>