X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 19A92D1B4D2
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 11 Feb 2004 08:38:46 +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 93816-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 11 Feb 2004 04:38:30 -0400 (AST)
Received: from crommail.cromwell-tools.co.uk
	(host217-40-164-180.in-addr.btopenworld.com [217.40.164.180])
	by svr1.postgresql.org (Postfix) with ESMTP id F0646D1B897
	for <pgsql-sql@postgresql.org>; Wed, 11 Feb 2004 04:38:26 -0400 (AST)
Message-ID: <4029EA3C.5000509@cromwell.co.uk>
Date: Wed, 11 Feb 2004 08:39:24 +0000
From: Mark Gibson <gibsonm@cromwell.co.uk>
MIME-Version: 1.0
To: David B <postgresql@thegatelys.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Elegant way to monitor for changes in a trigger and migrate
References: <GOEGKICPFOPNLIEIHGFJMEHHCHAA.postgresql@thegatelys.com>
In-Reply-To: <GOEGKICPFOPNLIEIHGFJMEHHCHAA.postgresql@thegatelys.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/103
X-Sequence-Number: 16794

David B wrote:

>Folks,
>
>Perhaps you can help....hell I'm sure you can!
>I want to monitor for changes in a table and migrate the OLD. record to
>audit table.
>
>Is there an elegant or generic way to do this so I can use across multiple
>tables with little change.
>  
>

You can use a rule to do this:

CREATE RULE cust_audit AS ON UPDATE OR DELETE TO cust DO
  INSERT INTO cust_hist SELECT OLD.*;

cust_hist should be identical to cust without a primary key or any 
constraints/foreign keys etc.

I'm currently working on an auditing system at present, and will be 
releasing it
soon if anyone is interested. It needs some cleaning up first, when I 
have time.

-- 
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.