From: | Sonam Sharma <sonams1209(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Trigger |
Date: | 2020-02-25 08:01:13 |
Message-ID: | CAM-M3TkOWhUsWPtgDhaNbrNTKeHXX2mykxHaEbM_ZSQe+ozZdA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 ..
Thanks,
Sonam
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2020-02-25 09:20:59 | Re: LDAP with TLS is taking more time in Postgresql 11.5 |
Previous Message | sivapostgres@yahoo.com | 2020-02-25 01:55:48 | Re: Backup & Restore |