469,609 Members | 1,130 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

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

---------------------------(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

Nov 23 '05 #1
3 3314
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

Nov 23 '05 #2
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

Nov 23 '05 #3
"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

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by takilroy | last post: by
18 posts views Thread by Bill Smith | last post: by
3 posts views Thread by Ricardo Corsi | last post: by
6 posts views Thread by Robert Fitzpatrick | last post: by
13 posts views Thread by dennis | last post: by
2 posts views Thread by paulmac106 | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.