Re: triggers: how to check if a field changed?

From: "Lance Arlaus" <lance(dot)nospam(dot)1(at)codeberet(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: triggers: how to check if a field changed?
Date: 2005-08-30 04:25:36
Message-ID: 000b01c5ad1a$df752b00$6401a8c0@LanceLaptop
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

The following assumes you're writing the trigger in PL/pgSQL, but there are
similar facilities in the other supported langs.

There are two records (OLD and NEW) that are pre-defined in trigger
procedures. Simply compare the fields on those records.
For example (assuming field is non-nullable):

IF (OLD.p_msg_raw <> NEW.p_msg_raw) THEN
-- Reindex
ENDIF

Doc reference:
http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of me(at)alternize(dot)com
Sent: Monday, August 29, 2005 8:47 PM
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] triggers: how to check if a field changed?

hi list

for our discussion board i've successfully set up a trigger to have tsearch2
index the record every time it is updated. this works fine, but also
triggers the reindex of the record every time i update the "# of views"
counter, which creates unnecessary load. in a trigger, is there a way to
check the fields affected by the UPDATE query? i only want to have the
tsearch2 index updated when the indexed field (p_msg_raw) changed...

this is the current trigger (straight following the tsearch2 guide):

CREATE TRIGGER "posts_ts_update" BEFORE INSERT OR UPDATE
ON "forum"."posts" FOR EACH ROW
EXECUTE PROCEDURE "public"."tsearch2"(idxfti, p_msg_raw);

thanks in advance,
thomas

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2005-08-30 04:42:43 Re: Disabling Trigger
Previous Message Michael Fuhr 2005-08-30 03:17:48 Re: Fwd: Re: question - plpgsql and query on table given by variable