Обсуждение: Sequences, triggers and 'OLD' - am I being stupid?
I'll admit straight away I'm a novice, but this one has me perplexed.... I have a table, T1, with a column (userid) that is an int4 generated from a sequence. I have a second table (T2) that I wish to use to hold audited values from T1. I have created a trigger function written in PL/PGSQL, set to be for each row after update on T2, which is supposed to copy the OLD values into T2. The snag is that when I try to update a row in T1 I get: ERROR: record "old" has no field "userid" This behaviour does not seem to be documented anywhere. Am I just being dense? TIA, Steve S -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.13/123 - Release Date: 06/10/2005
Steve,
I'm not quite sure I understand. It seems that the trigger should be on table
T1 not T2. The trigger should fire on an update to T1.
Regardless of that, OLD is only available during an UPDATE or DELETE
operations. OLD is NOT available during an INSERT operation. NEW is available
in all three instances. Use TG_OP to distinguish what is happening and then
don't use references to OLD in the INSERT block.
IF TG_OP = ''Insert'' THEN
<only references to NEW here>
ELSIF TG_OP = ''UPDATE'' THEN
<references to both OLD and NEW here>
END IF;
You might need to post some of the code.
HTH.
On Friday 07 October 2005 08:59 am, Steve South saith:
> I'll admit straight away I'm a novice, but this one has me perplexed....
>
> I have a table, T1, with a column (userid) that is an int4 generated from a
> sequence.
>
> I have a second table (T2) that I wish to use to hold audited values from
> T1.
>
> I have created a trigger function written in PL/PGSQL, set to be for each
> row after update on T2, which is supposed to copy the OLD values into T2.
>
> The snag is that when I try to update a row in T1 I get:
>
> ERROR: record "old" has no field "userid"
>
> This behaviour does not seem to be documented anywhere. Am I just being
> dense?
>
> TIA,
>
> Steve S
>
> --
> No virus found in this outgoing message.
> Checked by AVG Anti-Virus.
> Version: 7.0.344 / Virus Database: 267.11.13/123 - Release Date: 06/10/2005
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
--
>Steve,
> I'm not quite sure I understand. It seems that the trigger should be on
table
> T1 not T2. The trigger should fire on an update to T1.
> Regardless of that, OLD is only available during an UPDATE or DELETE
> operations. OLD is NOT available during an INSERT operation. NEW is
available
> in all three instances. Use TG_OP to distinguish what is happening and
then
> don't use references to OLD in the INSERT block.
> IF TG_OP = ''Insert'' THEN
> <only references to NEW here>
> ELSIF TG_OP = ''UPDATE'' THEN
> <references to both OLD and NEW here>
> END IF;
> You might need to post some of the code.
Oh dear. First post to the list and I make a typo :(
Of course, the trigger is fired on update to on T1:
CREATE OR REPLACE FUNCTION "TAuditUser"()
RETURNS "trigger" AS
$BODY$
begin
insert into "UserTA" (
"UserID",
"UserShortName",
"UserLongName",
"EnteredBy",
"EntryTS",
"AuditComment")
values (
OLD."UserId",
OLD."UserShortName",
OLD."UserLongName",
OLD."EnteredBy",
OLD."EntryTS",
OLD."AuditComment");
<snip>
... and...
CREATE TRIGGER "TrigUserUpdate"
AFTER UPDATE
ON "UserT"
FOR EACH ROW
EXECUTE PROCEDURE "TAuditUser"();
... and...
CREATE TABLE "UserT"
(
"UserID" int4 NOT NULL DEFAULT nextval('public."UserT_UserID_seq"'::text),
"UserShortName" varchar(32) NOT NULL,
"UserLongName" varchar(128) NOT NULL,
"HasAudit" bool NOT NULL,
"EnteredBy" int4 NOT NULL,
"EntryTS" timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with time
zone,
"AuditComment" varchar(1024) NOT NULL
)
WITHOUT OIDS;
When trying to update a row in UserT I get:
update "UserT" set "UserShortName" = 'Joe Blogg' where "UserID" = 1
ERROR: record "old" has no field "userid"
CONTEXT: PL/pgSQL function "TAuditUser" line 2 at SQL statement
Cheers,
Steve S
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.13/123 - Release Date: 06/10/2005
Tom, You have no idea how many times I looked at that and did not spot it. Excuse me while I just go and wipe the egg off... Problem solved. Cheers, Steve -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.13/123 - Release Date: 06/10/2005
Steve,
I'm sorry, but I don't see what the problem could be. I am not familiar with
the double quotes around the column names, but then, I'm still on version
7.4.6. Someone with more knowledge than I will have to answer this one. You
may want to post this to the general list.
Sorry...
On Friday 07 October 2005 09:47 am, Steve South saith:
> >Steve,
> >
> > I'm not quite sure I understand. It seems that the trigger should be on
>
> table
>
> > T1 not T2. The trigger should fire on an update to T1.
> >
> > Regardless of that, OLD is only available during an UPDATE or DELETE
> > operations. OLD is NOT available during an INSERT operation. NEW is
>
> available
>
> > in all three instances. Use TG_OP to distinguish what is happening and
>
> then
>
> > don't use references to OLD in the INSERT block.
> >
> > IF TG_OP = ''Insert'' THEN
> > <only references to NEW here>
> > ELSIF TG_OP = ''UPDATE'' THEN
> > <references to both OLD and NEW here>
> > END IF;
> >
> > You might need to post some of the code.
>
> Oh dear. First post to the list and I make a typo :(
>
> Of course, the trigger is fired on update to on T1:
>
> CREATE OR REPLACE FUNCTION "TAuditUser"()
> RETURNS "trigger" AS
> $BODY$
> begin
> insert into "UserTA" (
> "UserID",
> "UserShortName",
> "UserLongName",
> "EnteredBy",
> "EntryTS",
> "AuditComment")
> values (
> OLD."UserId",
> OLD."UserShortName",
> OLD."UserLongName",
> OLD."EnteredBy",
> OLD."EntryTS",
> OLD."AuditComment");
>
> <snip>
>
> ... and...
>
> CREATE TRIGGER "TrigUserUpdate"
> AFTER UPDATE
> ON "UserT"
> FOR EACH ROW
> EXECUTE PROCEDURE "TAuditUser"();
>
> ... and...
>
> CREATE TABLE "UserT"
> (
> "UserID" int4 NOT NULL DEFAULT
> nextval('public."UserT_UserID_seq"'::text), "UserShortName" varchar(32) NOT
> NULL,
> "UserLongName" varchar(128) NOT NULL,
> "HasAudit" bool NOT NULL,
> "EnteredBy" int4 NOT NULL,
> "EntryTS" timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with
> time zone,
> "AuditComment" varchar(1024) NOT NULL
> )
> WITHOUT OIDS;
>
> When trying to update a row in UserT I get:
>
> update "UserT" set "UserShortName" = 'Joe Blogg' where "UserID" = 1
>
>
> ERROR: record "old" has no field "userid"
> CONTEXT: PL/pgSQL function "TAuditUser" line 2 at SQL statement
>
> Cheers,
>
> Steve S
>
> --
> No virus found in this outgoing message.
> Checked by AVG Anti-Virus.
> Version: 7.0.344 / Virus Database: 267.11.13/123 - Release Date: 06/10/2005
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
"Steve South" <steve.south@blueyonder.co.uk> writes:
> ERROR: record "old" has no field "userid"
I think you're just being sloppy about capitalization:
> OLD."UserId",
> "UserID" int4 NOT NULL DEFAULT nextval('public."UserT_UserID_seq"'::text),
"UserID" != "UserId".
Also, the error message suggests strongly that you didn't double-quote
the name in at least one place in the trigger, so that it got folded
to lower case.
regards, tom lane