Thread: Trigger isn't fired

Trigger isn't fired

From

Együd Csaba

Date:

26 June 2004, 18:31:25

Hi,
I created a trigger function and a trigger with that function on a table. It
is an "after insert or update or delete" trigger, in which I want to modify
a second table regardless of the current modifications.

Everything seems to be good on modifying the table, no error messages. After
the modifications the second table isn't modified at all. Should anybody
suggest me what I'm doing wrong? Does a trigger functon send error messages
if it fails?

My statements:
--------------
CREATE OR REPLACE FUNCTION trg_upd_prod_instock() RETURNS trigger AS '
BEGIN
  perform ''update t_products set
getup_instock=get_stock_getup(''||quote_literal(NEW.productid)||'',1) where
id=''||quote_literal(NEW.productid);
  RETURN NEW;
END;
' LANGUAGE plpgsql;

GRANT ALL ON FUNCTION trg_upd_prod_instock() TO public;

CREATE TRIGGER t_stockchanges_trigger AFTER INSERT OR UPDATE OR DELETE ON
t_stockchanges
    FOR EACH ROW EXECUTE PROCEDURE trg_upd_prod_instock();

Many thanks,
  -- Csaba Együd

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.707 / Virus Database: 463 - Release Date: 2004. 06. 15.

Re: Trigger isn't fired

From

Stephan Szabo

Date:

26 June 2004, 20:32:16

On Sat, 26 Jun 2004, [iso-8859-2] Együd Csaba wrote:

> Hi,
> I created a trigger function and a trigger with that function on a table. It
> is an "after insert or update or delete" trigger, in which I want to modify
> a second table regardless of the current modifications.
>
> Everything seems to be good on modifying the table, no error messages. After
> the modifications the second table isn't modified at all. Should anybody
> suggest me what I'm doing wrong? Does a trigger functon send error messages
> if it fails?
>
> My statements:
> --------------
> CREATE OR REPLACE FUNCTION trg_upd_prod_instock() RETURNS trigger AS '
> BEGIN
>   perform ''update t_products set
> getup_instock=get_stock_getup(''||quote_literal(NEW.productid)||'',1) where
> id=''||quote_literal(NEW.productid);

I don't think this is going to do what you want.  Are you sure you didn't
want EXECUTE rather than PERFORM?

Re: Trigger isn't fired

From

Együd Csaba

Date:

27 June 2004, 06:58:48

Hi Stephan,
it was the problem, but not realy understand the reason. The documentetion
sais that perform doesn't porvide the return value and I thought I do not
need that. But I read absently, and didn't realized that the query to
perform must be a SELECT.

Thank you for opening my eyes. :) Now the trigger does its task. Thank you
very much.

Best regards,
  -- Csaba Együd


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Stephan Szabo
> Sent: 2004. június 26. 22:32
> To: Együd Csaba
> Cc: Pgsql-General@Postgresql.Org (E-mail)
> Subject: Re: [GENERAL] Trigger isn't fired
>
>
> On Sat, 26 Jun 2004, [iso-8859-2] Együd Csaba wrote:
>
> > Hi,
> > I created a trigger function and a trigger with that
> function on a table. It
> > is an "after insert or update or delete" trigger, in which
> I want to modify
> > a second table regardless of the current modifications.
> >
> > Everything seems to be good on modifying the table, no
> error messages. After
> > the modifications the second table isn't modified at all.
> Should anybody
> > suggest me what I'm doing wrong? Does a trigger functon
> send error messages
> > if it fails?
> >
> > My statements:
> > --------------
> > CREATE OR REPLACE FUNCTION trg_upd_prod_instock() RETURNS
> trigger AS '
> > BEGIN
> >   perform ''update t_products set
> >
> getup_instock=get_stock_getup(''||quote_literal(NEW.productid)
> ||'',1) where
> > id=''||quote_literal(NEW.productid);
>
> I don't think this is going to do what you want.  Are you
> sure you didn't
> want EXECUTE rather than PERFORM?
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.707 / Virus Database: 463 - Release Date: 2004. 06. 15.
>

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.707 / Virus Database: 463 - Release Date: 2004. 06. 15.

Re: Trigger isn't fired

From

Harald Fuchs

Date:

27 June 2004, 10:12:30

In article <016601c45c14$136a9570$230a0a0a@compaq>,
Együd Csaba <csegyud@vnet.hu> writes:

> Hi Stephan,
> it was the problem, but not realy understand the reason. The documentetion
> sais that perform doesn't porvide the return value and I thought I do not
> need that. But I read absently, and didn't realized that the query to
> perform must be a SELECT.

> Thank you for opening my eyes. :) Now the trigger does its task.

Does it really?  You're using NEW.productid, which should be undefined
for a DELETE trigger.

Re: Trigger isn't fired

From

Együd Csaba

Date:

27 June 2004, 12:13:36

Yes, yes you are right. :)
I had already modified that (using TG_OP variable) before I posted the
letter just didn't mentioned.
By the way the trigger does its task. Rally. :)

Thanks,
  -- Csaba

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Harald Fuchs
> Sent: 2004. június 27. 12:12
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Trigger isn't fired
>
>
> In article <016601c45c14$136a9570$230a0a0a@compaq>,
> Együd Csaba <csegyud@vnet.hu> writes:
>
> > Hi Stephan,
> > it was the problem, but not realy understand the reason.
> The documentetion
> > sais that perform doesn't porvide the return value and I
> thought I do not
> > need that. But I read absently, and didn't realized that
> the query to
> > perform must be a SELECT.
>
> > Thank you for opening my eyes. :) Now the trigger does its task.
>
> Does it really?  You're using NEW.productid, which should be undefined
> for a DELETE trigger.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.712 / Virus Database: 468 - Release Date: 2004. 06. 27.
>

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.712 / Virus Database: 468 - Release Date: 2004. 06. 27.