Trigger Update Issue

[beer]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[beer]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Tom Lane]

"beer" <be**@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org