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