Обсуждение: 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_CDand 
>> 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
errorpretty 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_CDand
 
>>> 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
theirerror 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