X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 4826AD1E825
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 18 Jan 2004 22:26:34 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 61476-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 18 Jan 2004 18:26:05 -0400 (AST)
Received: from ndl1mr1-a-fixed.sancharnet.in (ndl1mr1-a-fixed.sancharnet.in
	[61.0.0.45])
	by svr1.postgresql.org (Postfix) with ESMTP id 255E6D1E814
	for <pgsql-sql@postgresql.org>; Sun, 18 Jan 2004 18:26:03 -0400 (AST)
Received: from conversion-daemon.ndl1mr1-a-fixed.sancharnet.in by
	ndl1mr1-a-fixed.sancharnet.in
	(iPlanet Messaging Server 5.2 HotFix 1.17 (built Jun 23 2003))
	id <0HRP00001HT0ZB@ndl1mr1-a-fixed.sancharnet.in> for
	pgsql-sql@postgresql.org; Mon, 19 Jan 2004 03:56:04 +0530 (IST)
Received: from sancharnet.in ([61.0.95.126]) by ndl1mr1-a-fixed.sancharnet.in
	(iPlanet Messaging Server 5.2 HotFix 1.17 (built Jun 23 2003))
	with ESMTPA id <0HRP000YUI58HD@ndl1mr1-a-fixed.sancharnet.in>; Mon,
	19 Jan 2004 03:56:04 +0530 (IST)
Date: Mon, 19 Jan 2004 03:59:00 +0530
From: "Vishal Kashyap @ [Sai Hertz And Control Systems]"
 <sank89@sancharnet.in>
Subject: Re: Trigger to identify which column(s) updated
In-reply-to: <ENEIKKAEJOFHOIHMMOIDKEAFCJAA.jack.kerkhof@guest-tek.com>
To: Jack Kerkhof <jack.kerkhof@guest-tek.com>
Cc: pgsql-sql@postgresql.org
Reply-To: aspire420@hotpop.com
Message-id: <400B08AC.8040607@sancharnet.in>
Organization: Sai Hertz And Control Systems
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
X-Accept-Language: en-us, en
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6b)
	Gecko/20031205 Thunderbird/0.4
References: <ENEIKKAEJOFHOIHMMOIDKEAFCJAA.jack.kerkhof@guest-tek.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200401/154
X-Sequence-Number: 16547

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.
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*