Thread: Sequences, triggers and 'OLD' - am I being stupid?

Sequences, triggers and 'OLD' - am I being stupid?

From
"Steve South"
Date:
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



Re: Sequences, triggers and 'OLD' - am I being stupid?

From
Terry Lee Tucker
Date:
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

--

Re: Sequences, triggers and 'OLD' - am I being stupid?

From
"Steve South"
Date:
>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



Re: Sequences, triggers and 'OLD' - am I being stupid?

From
"Steve South"
Date:
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



Re: Sequences, triggers and 'OLD' - am I being stupid?

From
Terry Lee Tucker
Date:
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

Re: Sequences, triggers and 'OLD' - am I being stupid?

From
Tom Lane
Date:
"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