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

>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