pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Re: trigger error
3+ messages / 3 participants
[nested] [flat]

* trigger error
@ 2003-06-10 16:04 Yudie <yudie@axiontech.com>
  2003-06-10 15:57 ` Re: trigger error Dmitry Tkach <dmitry@openratings.com>
  2003-06-10 16:27 ` Re: trigger error Mendola Gaetano <mendola@bigfoot.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Yudie @ 2003-06-10 16:04 UTC (permalink / raw)
  To: pgsql-sql

Hi,
I',m trying to create trigger with plpgsql trigger function
then I got this error message when trigger executed:

Number: -2147467259
Error while executing th query;
ERROR: fmgr_info: function 1546856080: cache lookup failed

Here is the function code:

CREATE FUNCTION TRIGGER_UPDATE_AXPRDT_STATUS()
RETURNS OPAQUE
AS 'BEGIN
  IF NEW.ONHAND = 0  THEN
    NEW.STATUS = ''D'';
  END IF;
  RETURN NEW;
END;'
LANGUAGE 'plpgsql';

CREATE TRIGGER TRIGGER_AXPRDT_FUTURESTATUS
BEFORE UPDATE ON AXPRDT
FOR EACH ROW
EXECUTE PROCEDURE TRIGGER_UPDATE_AXPRDT_STATUS();


Please Help.. 

Yudie

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: trigger error
  2003-06-10 16:04 trigger error Yudie <yudie@axiontech.com>
@ 2003-06-10 15:57 ` Dmitry Tkach <dmitry@openratings.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Dmitry Tkach @ 2003-06-10 15:57 UTC (permalink / raw)
  To: Yudie <yudie@axiontech.com>; +Cc: pgsql-sql

You must have dropped and recreated the function after your trigger was 
created...
You need to recreate the trigger now, so that it picks up the new 
function id.
In the future use 'CREATE OR REPLACE' to modify a function instead of 
DROP and CREATE - this will make sure the modified function keeps its id.

I hope, it helps...

Dima

Yudie wrote:

> Hi,
> I',m trying to create trigger with plpgsql trigger function
> then I got this error message when trigger executed:
>  
> Number: -2147467259
> Error while executing th query;
> ERROR: fmgr_info: function 1546856080: cache lookup failed
>  
> Here is the function code:
>  
> CREATE FUNCTION TRIGGER_UPDATE_AXPRDT_STATUS()
> RETURNS OPAQUE
> AS 'BEGIN
>   IF NEW.ONHAND = 0  THEN
>     NEW.STATUS = ''D'';
>   END IF;
>   RETURN NEW;
> END;'
> LANGUAGE 'plpgsql';
>  
> CREATE TRIGGER TRIGGER_AXPRDT_FUTURESTATUS
> BEFORE UPDATE ON AXPRDT
> FOR EACH ROW
> EXECUTE PROCEDURE TRIGGER_UPDATE_AXPRDT_STATUS();
>  
>  
> Please Help..
>  
> Yudie






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: trigger error
  2003-06-10 16:04 trigger error Yudie <yudie@axiontech.com>
@ 2003-06-10 16:27 ` Mendola Gaetano <mendola@bigfoot.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Mendola Gaetano @ 2003-06-10 16:27 UTC (permalink / raw)
  To: Yudie <yudie@axiontech.com>; pgsql-sql

"Yudie" <yudie@axiontech.com> wrote:
> Hi,
> I',m trying to create trigger with plpgsql trigger function
> then I got this error message when trigger executed:

> Number: -2147467259
> Error while executing th query;
> ERROR: fmgr_info: function 1546856080: cache lookup failed

> Here is the function code:

>CREATE FUNCTION TRIGGER_UPDATE_AXPRDT_STATUS()
>RETURNS OPAQUE
>[snipped]
>LANGUAGE 'plpgsql';

>CREATE TRIGGER TRIGGER_AXPRDT_FUTURESTATUS
>BEFORE UPDATE ON AXPRDT
>FOR EACH ROW
>EXECUTE PROCEDURE TRIGGER_UPDATE_AXPRDT_STATUS();

The errors mean that after the trigger definition you maded a 
modification on the function rebuilding it. Try to delete the trigger and
define it again.

The fact that you are using "RETURN OPAQUE" instead of
"RETURN TRIGGER" means that you are using a Postgres 
version prior then 7.3.x.
Using the version 7.3.x ( I suggest you the 7.3.3 ) you can 
define that funcion: "CREATE OR REPLACE .... " so you are
not forced to delete it before and your problem should gone.


Gaetano




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2003-06-10 16:27 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-10 16:04 trigger error Yudie <yudie@axiontech.com>
2003-06-10 15:57 ` Dmitry Tkach <dmitry@openratings.com>
2003-06-10 16:27 ` Mendola Gaetano <mendola@bigfoot.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox