From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Sonam Sharma <sonams1209(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Trigger |
Date: | 2020-02-25 16:14:59 |
Message-ID: | 17412b0b-d594-a96f-e93c-21ef638b7726@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2/25/20 12:01 AM, Sonam Sharma wrote:
> I have a trigger, like many other triggers that fire after
>
> update and checks a field of the OLD set. For some reason this trigger
> throw this error:
>
> ERROR: record "old" has no field "ivo_sts_cd" CONTEXT: SQL statement
>
> "UPDATE dbo.T8071_CAI_IVO_HDR SET IVO_STS_CD = 1 where
> T616_VBU_NBR=old.T616_VBU_NBR
>
> and T617_FNC_TYP_CD=old.T617_FNC_TYP_CD and
> T8071_CAI_IVO_ID=old.T8071_CAI_IVO_ID and
>
> T8071_ADD_DM= old. T8071_ADD_DM and old.ivo_sts_cd != 10 and
> old.ivo_sts_cd != 3" PL/pgSQL
>
> function dbo.t8096_upd_func02() line 9 at SQL statement SQL state: 42703
>
> this is the trigger :
>
> t8096_upd_trig02 AFTER UPDATE ON dbo.t8096_cai_ivo_exc FOR EACH ROW WHEN
> (old.exc_rsd_idc IS DISTINCT FROM new.exc_rsd_idc) EXECUTE PROCEDURE
> dbo.t8096_upd_func02()
>
> below is the function :
>
> CREATE OR REPLACE FUNCTION pps.t8096_upd_func02()
>
> RETURNS trigger
>
> LANGUAGE plpgsql
>
> AS $function$
>
> DECLARE
>
> exc_count int;
>
> BEGIN
>
> select count(*) into exc_count from pps.T8096_CAI_IVO_EXC WHERE
> T616_VBU_NBR=old.T616_VBU_NBR
>
> and T617_FNC_TYP_CD=old.T617_FNC_TYP_CD and
> T8071_CAI_IVO_ID=old.T8071_CAI_IVO_ID and T8071_ADD_DM= old.T8071_ADD_DM
> and EXC_RSd_IDC = 'N'
>
> AND T8095_EXC_TYP_CD NOT IN(8,9,10,11) ;
>
> if exc_count = 0 then
>
> UPDATE pps.T8071_CAI_IVO_HDR SET IVO_STS_CD = 1 where
> T616_VBU_NBR=old.T616_VBU_NBR and T617_FNC_TYP_CD=old.T617_FNC_TYP_CD and
>
> T8071_CAI_IVO_ID=old.T8071_CAI_IVO_ID and T8071_ADD_DM= old.
> T8071_ADD_DM and old.ivo_sts_cd != 10 and old.ivo_sts_cd != 3;
>
> end if;
>
> RETURN NEW;
>
> END
>
> $function$;
>
>
> Can someone please help where I am missing ..
Does the table dbo.t8096_cai_ivo_exc have the field ivo_sts_cd?
>
>
> Thanks,
>
> Sonam
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Dipanjan Ganguly | 2020-02-25 16:28:38 | Connections dropping while using Postgres backend DB with Ejabberd |
Previous Message | Adrian Klaver | 2020-02-25 15:54:31 | Re: LDAP with TLS is taking more time in Postgresql 11.5 |