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