pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Trigger from a function
3+ messages / 2 participants
[nested] [flat]

* Trigger from a function
@ 2008-02-18 16:29 dguevara <dguevara@acceso.com.pe>
  2008-02-21 23:31 ` Re: Trigger from a function Decibel! <decibel@decibel.org>
  0 siblings, 1 reply; 3+ messages in thread

From: dguevara @ 2008-02-18 16:29 UTC (permalink / raw)
  To: pgsql-admin

Hello all,

I have a problem with a trigger which is executed when i make an UPDATE trought a simple query, but
not when the UPDATE is made in a store procedure.

Maybe i'm missing something in the declaration of the function and i would appreciate any help.
T
his is the function:

CREATE OR REPLACE FUNCTION X
  RETURNS SETOF void AS
$BODY$

begin
  update A;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


The trigger:

CREATE TRIGGER Y
  BEFORE INSERT OR UPDATE
  ON A
  FOR EACH ROW
  EXECUTE PROCEDURE Z;

CREATE OR REPLACE FUNCTION Z
  RETURNS "trigger" AS
$BODY$
begin
     if (tg_op in ('INSERT', 'UPDATE', 'DELETE')) then
        update A;
     end if;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


Thanks in advance.
Danilo Guevara
Departamento de Sistemas
Acceso Crediticio SAC




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

* Re: Trigger from a function
  2008-02-18 16:29 Trigger from a function dguevara <dguevara@acceso.com.pe>
@ 2008-02-21 23:31 ` Decibel! <decibel@decibel.org>
  2008-02-21 23:49   ` Re: Trigger from a function dguevara <dguevara@acceso.com.pe>
  0 siblings, 1 reply; 3+ messages in thread

From: Decibel! @ 2008-02-21 23:31 UTC (permalink / raw)
  To: dguevara <dguevara@acceso.com.pe>; +Cc: pgsql-admin

On Feb 18, 2008, at 10:29 AM, dguevara wrote:

> Hello all,
>
> I have a problem with a trigger which is executed when i make an  
> UPDATE trought a simple query, but
> not when the UPDATE is made in a store procedure.
>
> Maybe i'm missing something in the declaration of the function and  
> i would appreciate any help.
> T
> his is the function:
>
> CREATE OR REPLACE FUNCTION X
>   RETURNS SETOF void AS
> $BODY$
>
> begin
>   update A;
> end;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE;
>
>
> The trigger:
>
> CREATE TRIGGER Y
>   BEFORE INSERT OR UPDATE
>   ON A
>   FOR EACH ROW
>   EXECUTE PROCEDURE Z;
>
> CREATE OR REPLACE FUNCTION Z
>   RETURNS "trigger" AS
> $BODY$
> begin
>      if (tg_op in ('INSERT', 'UPDATE', 'DELETE')) then
>         update A;
>      end if;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE;

Can't help much with your fake example, but I am wondering why you  
have a recursive trigger on A.
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Attachments:

  smime.p7s (application/pkcs7-signature, 2.5K) download

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

* Re: Trigger from a function
  2008-02-18 16:29 Trigger from a function dguevara <dguevara@acceso.com.pe>
  2008-02-21 23:31 ` Re: Trigger from a function Decibel! <decibel@decibel.org>
@ 2008-02-21 23:49   ` dguevara <dguevara@acceso.com.pe>
  0 siblings, 0 replies; 3+ messages in thread

From: dguevara @ 2008-02-21 23:49 UTC (permalink / raw)
  To: Decibel! <decibel@decibel.org>; +Cc: pgsql-admin

Thanks for your response, i made a mistake, the last function is like this:

CREATE OR REPLACE FUNCTION Z
   RETURNS "trigger" AS
 $BODY$
 begin
      if (tg_op in ('INSERT', 'UPDATE', 'DELETE')) then
         update B;
      end if;
 $BODY$
  LANGUAGE 'plpgsql' VOLATILE;


And the reason why i made a fake example is because it basically is that, a simple function with a
simple update. I'd just like to know why if  i do that update through a query it invokes the trigger
and not when i do it inside the function.


Danilo Guevara
Departamento de Sistemas
Acceso Crediticio SAC

---------- Original Message -----------
From: Decibel! <decibel@decibel.org>
To: dguevara <dguevara@acceso.com.pe>
Cc: pgsql-admin@postgresql.org
Sent: Thu, 21 Feb 2008 17:31:44 -0600
Subject: Re: [ADMIN] Trigger from a function

> On Feb 18, 2008, at 10:29 AM, dguevara wrote:
> 
> > Hello all,
> >
> > I have a problem with a trigger which is executed when i make an  
> > UPDATE trought a simple query, but
> > not when the UPDATE is made in a store procedure.
> >
> > Maybe i'm missing something in the declaration of the function and  
> > i would appreciate any help.
> > T
> > his is the function:
> >
> > CREATE OR REPLACE FUNCTION X
> >   RETURNS SETOF void AS
> > $BODY$
> >
> > begin
> >   update A;
> > end;
> > $BODY$
> >   LANGUAGE 'plpgsql' VOLATILE;
> >
> >
> > The trigger:
> >
> > CREATE TRIGGER Y
> >   BEFORE INSERT OR UPDATE
> >   ON A
> >   FOR EACH ROW
> >   EXECUTE PROCEDURE Z;
> >
> > CREATE OR REPLACE FUNCTION Z
> >   RETURNS "trigger" AS
> > $BODY$
> > begin
> >      if (tg_op in ('INSERT', 'UPDATE', 'DELETE')) then
> >         update A;
> >      end if;
> > $BODY$
> >   LANGUAGE 'plpgsql' VOLATILE;
> 
> Can't help much with your fake example, but I am wondering why you  
> have a recursive trigger on A.
> -- 
> Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
> Give your computer some brain candy! www.distributed.net Team #1828
------- End of Original Message -------




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


end of thread, other threads:[~2008-02-21 23:49 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-18 16:29 Trigger from a function dguevara <dguevara@acceso.com.pe>
2008-02-21 23:31 ` Decibel! <decibel@decibel.org>
2008-02-21 23:49   ` dguevara <dguevara@acceso.com.pe>

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