Trigger

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.klaver@aklaver.com

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

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;

Realized I went through the above to quickly. I do not see a SET, nor am
I clear what table you are trying to UPDATE.

end if;

RETURN NEW;

END

$function$;

Can someone please help where I am missing ..

Thanks,

Sonam

--
Adrian Klaver
adrian.klaver@aklaver.com

On 25 Feb 2020, at 17:53, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

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

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;

Realized I went through the above to quickly. I do not see a SET, nor am I clear what table you are trying to UPDATE.

I’m pretty sure that if the OP were to format their query in a more readable and consistent way, they would spot their error pretty quickly. It’s a simple typo.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

On 2/25/20 9:08 AM, Alban Hertroys wrote:

On 25 Feb 2020, at 17:53, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

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

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;

Realized I went through the above to quickly. I do not see a SET, nor am I clear what table you are trying to UPDATE.

I’m pretty sure that if the OP were to format their query in a more readable and consistent way, they would spot their error pretty quickly. It’s a simple typo.

Yeah, throwing it at:

http://sqlformat.darold.net/

returned:

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;

Found the SET:)

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

--
Adrian Klaver
adrian.klaver@aklaver.com