[SQL] PL/pgSQL triggers ON INSERT OR UPDATE

[san]

Hello, I'm trying to set up a trigger on insert or update
but when using the predefined variable ``OLD'' I get a
NOTICE from the trigger function about OLD not being defined yet.

Of course OLD is not defined when the function is triggered on INSERT
event, and I did not mention it if not inside a conditional block
checking for the TG_OP variable being 'UPDATE'.

For better understanding here is some code:

BEGIN
IF TG_OP = 'UPDATE' THEN
IF OLD.id <> NEW.id THEN
-- do the work
END IF
END IF;
END;

Even when TG_OP != 'UPDATE' (INSERT event) I still get an error
message from the pl/pgsql compiler (the first time the trigger is fired).

What should I do then ? Is it still possible to use the same function
for UPDATE OR INSERT events ?

TIA

--san;

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] PL/pgSQL triggers ON INSERT OR UPDATE

[san]

From [EMAIL PROTECTED]  Tue Oct 23 18:33:27 2001
Content-Disposition: inline
From: "Aasmund Midttun Godal" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: [SQL] PL/pgSQL triggers ON INSERT OR UPDATE
Date: Tue, 23 Oct 2001 16:31:12 GMT
X-Mailer: Googley-Moogley

First, I may be wrong but I do think they would prefer if you
did not cross-post (especially to hackers).

Second I think it probably make more sense to make two different
triggers here.

If you really wanted to do it that way you might want to try
executing that part.

Regards, Aasmund.

Thank you, I should have been *really* tired that night, but it is working
now ... No more complaints about missing OLD variable. It works as I wanted.

One trigger for both events, checks for TG_OP.

Sorry about cross-posting attempt ;)

please don't post that mail

--san;

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly