Thread: Trigger Update Issue

Hello All

I'm running 7.3.4-1 on a RH9 box.  I'm having a problem with a trigger
that seems to execute without actually performing the update that it
should.  The update returns true everytime however if it is the first time
that the trigger executes on a given row, the column is not updated.  The
column is updated correctly on subsequent calls.

Here is the code:

create table tabA (
  id     char(32) primary key not null,
  Acol1   char(40) not null unique,
  Acol2   integer not null,
  Acol3   integer default 0 check ( Acol3 >= 0),
);

create table tabB (
  id            integer default nextval('tabB_id_seq'::text)
                not null check (id > 0) primary key,
  tabA_id       char(32) not null references tabA (id)
                on delete cascade on update cascade,
  Bcol1          text default null,
  Bcol2          text default null,
);


CREATE OR REPLACE FUNCTION "tabB_postinsert" () RETURNS TRIGGER AS '
  --
  -- Actions to take after inserting into tabB
  --
  BEGIN
    -- Increment tabA.attachments
    UPDATE tabA SET Acol3 = Acol3 + 1 WHERE id = NEW.tabA_id;
    -- done
    RETURN NEW;
  END; ' LANGUAGE 'plpgsql';

CREATE TRIGGER "master_tabB_postinsert"
  AFTER INSERT ON "tabB"
  FOR EACH ROW EXECUTE PROCEDURE "tabB_postinsert" ();

I am using this same trigger structure on other tables without having any
issues.  Any insight would be greatly appreciated.

Thanks

-b

On Friday 05 March 2004 14:00, beer wrote:
> Hello All
>
> I'm running 7.3.4-1 on a RH9 box.  I'm having a problem with a trigger
> that seems to execute without actually performing the update that it
> should.  The update returns true everytime however if it is the first time
> that the trigger executes on a given row, the column is not updated.  The
> column is updated correctly on subsequent calls.

Perhaps put some debug code in and insert into tabB manually

> CREATE OR REPLACE FUNCTION "tabB_postinsert" () RETURNS TRIGGER AS '
>   --
>   -- Actions to take after inserting into tabB
>   --
DECLARE
  numrows int4;

>   BEGIN
>     -- Increment tabA.attachments
>     UPDATE tabA SET Acol3 = Acol3 + 1 WHERE id = NEW.tabA_id;
  GET DIAGNOSTICTS numrows = ROW_COUNT;
  RAISE NOTICE ''Updated % rows with id = %'',numrows, NEW.tabA_id;
>     -- done
>     RETURN NEW;
>   END; ' LANGUAGE 'plpgsql';

Insert into tabB from psql and you should see a NOTICE message telling you
what is happening.

--
  Richard Huxton
  Archonet Ltd

Richard

Thanks for the suggestion.  I had tried something similar using FOUND but
that didnt not give me the number of rows touched.

According to the output, 1 row was updated, however when I select on the
row the value is still 0.

-b

> On Friday 05 March 2004 14:00, beer wrote:
>> Hello All
>>
>> I'm running 7.3.4-1 on a RH9 box.  I'm having a problem with a trigger
>> that seems to execute without actually performing the update that it
>> should.  The update returns true everytime however if it is the first
>> time
>> that the trigger executes on a given row, the column is not updated.
>> The
>> column is updated correctly on subsequent calls.
>
> Perhaps put some debug code in and insert into tabB manually
>
>> CREATE OR REPLACE FUNCTION "tabB_postinsert" () RETURNS TRIGGER AS '
>>   --
>>   -- Actions to take after inserting into tabB
>>   --
> DECLARE
>   numrows int4;
>
>>   BEGIN
>>     -- Increment tabA.attachments
>>     UPDATE tabA SET Acol3 = Acol3 + 1 WHERE id = NEW.tabA_id;
>   GET DIAGNOSTICTS numrows = ROW_COUNT;
>   RAISE NOTICE ''Updated % rows with id = %'',numrows, NEW.tabA_id;
>>     -- done
>>     RETURN NEW;
>>   END; ' LANGUAGE 'plpgsql';
>
> Insert into tabB from psql and you should see a NOTICE message telling you
> what is happening.
>
> --
>   Richard Huxton
>   Archonet Ltd
>
>

"beer" <beer@cmu.edu> writes:
> I'm running 7.3.4-1 on a RH9 box.  I'm having a problem with a trigger
> that seems to execute without actually performing the update that it
> should.  The update returns true everytime however if it is the first time
> that the trigger executes on a given row, the column is not updated.  The
> column is updated correctly on subsequent calls.

I couldn't reproduce this.  I created the tables and trigger and then
did:

regression=# insert into tabA values('id1','col1', 2, 32);
INSERT 154119 1
regression=# insert into tabB values(1,'id1','col1','col2');
INSERT 154120 1
regression=# select * from tabA;
                id                |                  acol1                   | acol2 | acol3
----------------------------------+------------------------------------------+-------+-------
 id1                              | col1                                     |     2 |    33
(1 row)

regression=# insert into tabB values(2,'id1','col1','col2');
INSERT 154121 1
regression=# select * from tabA;
                id                |                  acol1                   | acol2 | acol3
----------------------------------+------------------------------------------+-------+-------
 id1                              | col1                                     |     2 |    34
(1 row)

It looks fine to me ... what are you doing differently?

            regards, tom lane