Coming from Oracle - trigger question

Started by Graeme Merrallalmost 24 years ago2 messagesgeneral
Jump to latest
#1Graeme Merrall
gbmerrall@aol.com

I'm mucking about playing with triggers and functions and in Oracle it's
possible to check that a trigger isn't actually acting on a field you want
to update.
The function below will update the 'dirty' column as long as you're not
performing an operation on the 'dirty' as part of your SQL statement

CREATE OR REPLACE TRIGGER before_nodes_update
before update on nodes
for each row
begin
if not updating('DIRTY') then
:new.dirty := 1;
:new.modified := sysdate;
end if;
end;

Hence, "UPDATE nodes SET text='updating' WHERE node=1" would fire the
trigger and "UPDATE nodes SET dirty=0 WHERE node=1" would not fire the
trigger since we're updating the dirty column in the query.

Now I'm after the same functionality in postgres. Is there a way around
this? I have a feeling that rules my acheive what I'm after but it seems a
bit overkill for a simple column update.

Cheers,
Graeme

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Graeme Merrall (#1)
Re: Coming from Oracle - trigger question

"Graeme Merrall" <gbmerrall@aol.com> writes:

The function below will update the 'dirty' column as long as you're not
performing an operation on the 'dirty' as part of your SQL statement

CREATE OR REPLACE TRIGGER before_nodes_update
before update on nodes
for each row
begin
if not updating('DIRTY') then

Hmm. The usual locution in Postgres is more like

if old.foo != new.foo then ...

Is that close enough to do what you want?

regards, tom lane