Skip site navigation (1) Skip section navigation (2)

PL/pgSQL triggers ON INSERT OR UPDATE

From: san(at)cobalt(dot)rmnet(dot)it
To: pgsql-hackers(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org, pgsql-novice(at)postgresql(dot)org
Subject: PL/pgSQL triggers ON INSERT OR UPDATE
Date: 2001-10-22 21:25:44
Message-ID: 200110222125.XAA30632@cobalt.rmnet.it (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-novicepgsql-sql
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;

Responses

pgsql-novice by date

Next:From: Josh BerkusDate: 2001-10-22 23:09:20
Subject: Re: Getting back the primary key after INSERT
Previous:From: Mihai GheorghiuDate: 2001-10-22 21:23:49
Subject: Re: Problems setting up PostgreSQL and Tomcat JDBC connection

pgsql-hackers by date

Next:From: Tom LaneDate: 2001-10-22 21:37:24
Subject: Re: CREATE TABLE AS / WITHOUT OIDs?
Previous:From: Bruce MomjianDate: 2001-10-22 20:54:03
Subject: Re: [GENERAL] To Postgres Devs : Wouldn't changing the select limit

pgsql-sql by date

Next:From: Esteban Gutierrez AbarzuaDate: 2001-10-22 22:35:22
Subject: cast
Previous:From: Joel BurtonDate: 2001-10-22 19:54:28
Subject: Re: oid's in views.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group