pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PL/pgSQL triggers ON INSERT OR UPDATE
3+ messages / 3 participants
[nested] [flat]

* PL/pgSQL triggers ON INSERT OR UPDATE
@ 2001-10-22 21:25  san@cobalt.rmnet.it
  0 siblings, 1 reply; 3+ messages in thread

From: san@cobalt.rmnet.it @ 2001-10-22 21:25 UTC (permalink / raw)
  To: pgsql-hackers@postgresql.org; pgsql-sql; pgsql-novice@postgresql.org

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;



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: PL/pgSQL triggers ON INSERT OR UPDATE
@ 2001-10-23 16:31  Aasmund Midttun Godal <postgresql@envisity.com>
  parent: san@cobalt.rmnet.it
  0 siblings, 0 replies; 3+ messages in thread

From: Aasmund Midttun Godal @ 2001-10-23 16:31 UTC (permalink / raw)
  To: san@cobalt.rmnet.it; +Cc: pgsql-sql

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.

On Mon, 22 Oct 2001 23:25:44 +0200, san@cobalt.rmnet.it wrote:
> 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

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: PL/pgSQL triggers ON INSERT OR UPDATE
@ 2001-10-23 19:23  san <san@cobalt.rmnet.it>
  0 siblings, 0 replies; 3+ messages in thread

From: san @ 2001-10-23 19:23 UTC (permalink / raw)
  To: postgresql@envisity.com; san@cobalt.rmnet.it; +Cc: pgsql-sql

	From postgresql@envisity.com  Tue Oct 23 18:33:27 2001
	Content-Disposition: inline
	From: "Aasmund Midttun Godal" <postgresql@envisity.com>
	To: san@cobalt.rmnet.it
	Cc: pgsql-sql@postgresql.org
	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;



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-10-23 19:23 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-22 21:25 PL/pgSQL triggers ON INSERT OR UPDATE san@cobalt.rmnet.it
2001-10-23 16:31 ` Aasmund Midttun Godal <postgresql@envisity.com>
2001-10-23 19:23 Re: PL/pgSQL triggers ON INSERT OR UPDATE san <san@cobalt.rmnet.it>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox