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

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