From: Vishal Kashyap @ [Sai Hertz And Control Systems] <sank89@sancharnet.in>
To: Jack Kerkhof <jack.kerkhof@guest-tek.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Trigger to identify which column(s) updated
Date: Mon, 19 Jan 2004 03:59:00 +0530
Message-ID: <400B08AC.8040607@sancharnet.in> (raw)
In-Reply-To: <ENEIKKAEJOFHOIHMMOIDKEAFCJAA.jack.kerkhof@guest-tek.com>
References: <ENEIKKAEJOFHOIHMMOIDKEAFCJAA.jack.kerkhof@guest-tek.com>
Dear Jack ,
>I suspect that the function would look something like;
>
>CREATE FUNCTION FIND_CHANGED_COLUMNS() RETURNS OPAQUE AS '
>BEGIN
>
> -- FOR EACH COLUMN IN THE RECORD:
> -- IF ( NEW COLUMNx <> OLD COLUMNx)
> -- LOG THE RECORD PRIMARY KEY, COLUMN NAME, OLD VALUE
>
> RETURN NEW;
>END;
>' LANGUAGE 'plpgsql';
>
>
Instead of using a trigger use a rule as
<code>
</code>
CREATE RULE log_allthat AS ON UPDATE TO table_to_scan WHERE
((NEW.coloum_1 != old.NEW.coloum_1) OR (NEW.coloum_2 !=
old.NEW.coloum_2)) OR (MORE COLUMNS DO
INSERT INTO log_changes_table (
PRIMARY_KEY,COLUMN_NAME,OLD_VALUE
) VALUES (
OLD.PRIMARY_KEY,OLD.COLUMN_NAME,OLD.OLD_VALUE
);
Yes you will have to create a table as log_changes_table or any name you
fancy such that data could be loged
</code>
Kindly shoot back if this helps.
--
Regards,
Vishal Kashyap
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
I Know you believe my words so logon to Jabber.org
and add vishalkashyap@jabber.org to your roster.
~*~*~*~*~*~*~*~*
I am usually called by the name Vishal Kashyap
but my Girl friend believes my name should be
Vishal CASH UP.This is because others love my
nature and my Girl friend loves my CASH.
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sank89@sancharnet.in, jack.kerkhof@guest-tek.com
Subject: Re: Trigger to identify which column(s) updated
In-Reply-To: <400B08AC.8040607@sancharnet.in>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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