From richard@ibisaustralia.com Fri Dec  17 06:20:56 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 7E2D03CBE44
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 17 Dec 2004 06:20:56 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 86426-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 17 Dec 2004 06:20:44 +0000 (GMT)
Received: from gizmo01ps.bigpond.com (gizmo01ps.bigpond.com [144.140.71.11])
	by svr1.postgresql.org (Postfix) with SMTP id C98613CBE3C
	for <pgsql-sql@postgresql.org>; Fri, 17 Dec 2004 06:20:43 +0000 (GMT)
Received: (qmail 3263 invoked from network); 17 Dec 2004 06:20:38 -0000
Received: from unknown (HELO PSMAM18.bigpond.com) (144.135.25.118)
	by gizmo01ps.bigpond.com with SMTP; 17 Dec 2004 06:20:38 -0000
Received: from pipp-p-144-134-197-42.prem.tmns.net.au ([144.134.197.42]) by
	PSMAM18.bigpond.com(MAM REL_3_4_2 297/12486474) with SMTP id
	12486474; Fri, 17 Dec 2004 16:20:38 +1000
From: "Richard Sydney-Smith" <richard@ibisaustralia.com>
To: <pgsql-sql@postgresql.org>
Subject: Table History
Date: Fri, 17 Dec 2004 14:23:32 +0800
Message-ID: <002101c4e400$f0164220$13dd8a90@athlon2000>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0022_01C4E443.FE398220"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.2627
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
Importance: Normal
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.384 tagged_above=0 required=5 tests=HTML_60_70,
	HTML_MESSAGE, HTML_TEXT_AFTER_BODY, HTML_TEXT_AFTER_HTML
X-Spam-Level: 
X-Archive-Number: 200412/113
X-Sequence-Number: 19733

This is a multi-part message in MIME format.

------=_NextPart_000_0022_01C4E443.FE398220
Content-Type: text/plain;
	charset="Windows-1252"
Content-Transfer-Encoding: 7bit

A database I maintain has a central transaction table ( FTRANS)
I would like an second table FTRANS_IMAGE to maintain a historical view
of ftrans. So if records in FTRANS are altered we can trace the effect
of those changes in FTRANS_IMAGE.
 
I expect this has been done MANY times and I wonder if a general purpose
trigger exists or if not then can someone point me to an example set of
triggers?
 
I would like to trace an action code "Edit" or "Delete" in FTRANS_IMAGE
in additional to the common fields.
 
thanks muchly
 
Richard Sydney-Smith

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.799 / Virus Database: 543 - Release Date: 19/11/2004
 

------=_NextPart_000_0022_01C4E443.FE398220
Content-Type: text/html;
	charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3DWindows-1252">
<TITLE>Message</TITLE>

<META content=3D"MSHTML 6.00.2800.1479" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D718371606-17122004><FONT face=3DArial size=3D2>A =
database I=20
maintain has a central transaction table ( FTRANS)</FONT></SPAN></DIV>
<DIV><SPAN class=3D718371606-17122004><FONT face=3DArial size=3D2>I =
would like an=20
second table FTRANS_IMAGE to maintain a historical view of ftrans. So if =
records=20
in FTRANS are altered we can trace the effect of those changes in=20
FTRANS_IMAGE.</FONT></SPAN></DIV>
<DIV><SPAN class=3D718371606-17122004><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D718371606-17122004><FONT face=3DArial size=3D2>I =
expect this has=20
been done MANY times and I wonder if a general purpose trigger exists or =
if not=20
then can someone point me to an example set of =
triggers?</FONT></SPAN></DIV>
<DIV><SPAN class=3D718371606-17122004><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D718371606-17122004><FONT face=3DArial size=3D2>I =
would like to=20
trace an action code "Edit" or "Delete" in FTRANS_IMAGE in additional to =
the=20
common fields.</FONT></SPAN></DIV>
<DIV><SPAN class=3D718371606-17122004><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D718371606-17122004><FONT face=3DArial size=3D2>thanks =

muchly</FONT></SPAN></DIV>
<DIV><SPAN class=3D718371606-17122004><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D718371606-17122004><FONT face=3DArial =
size=3D2>Richard=20
Sydney-Smith</FONT></SPAN></DIV></BODY></HTML>
<BR>

<P><FONT SIZE=3D2>---<BR>
Outgoing mail is certified Virus Free.<BR>
Checked by AVG anti-virus system (http://www.grisoft.com).<BR>
Version: 6.0.799 / Virus Database: 543 - Release Date: 19/11/2004<BR>
</FONT> </P>

------=_NextPart_000_0022_01C4E443.FE398220--




From desoi@pgedit.com Fri Dec  17 15:37:57 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 211053CC995
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 17 Dec 2004 15:37:57 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 18104-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 17 Dec 2004 15:37:43 +0000 (GMT)
Received: from a2s1.a2webhosting.com (host-180.a2webhosting.com
 [69.39.89.180])
	by svr1.postgresql.org (Postfix) with ESMTP id 003193CC167
	for <pgsql-sql@postgresql.org>; Fri, 17 Dec 2004 15:37:46 +0000 (GMT)
Received: from user-vcaui2l.dsl.mindspring.com ([216.175.72.85]
	helo=[192.168.1.10])
	by a2s1.a2webhosting.com with esmtpsa (TLSv1:RC4-SHA:128) (Exim 4.43)
	id 1CfKAs-00054z-Kp; Fri, 17 Dec 2004 10:37:46 -0500
In-Reply-To: <002101c4e400$f0164220$13dd8a90@athlon2000>
References: <002101c4e400$f0164220$13dd8a90@athlon2000>
Mime-Version: 1.0 (Apple Message framework v619)
Content-Type: text/plain; charset=US-ASCII; format=flowed
Message-Id: <9A96163A-5041-11D9-B854-000A95B03262@pgedit.com>
Content-Transfer-Encoding: 7bit
Cc: <pgsql-sql@postgresql.org>
From: John DeSoi <desoi@pgedit.com>
Subject: Re: Table History
Date: Fri, 17 Dec 2004 10:37:48 -0500
To: "Richard Sydney-Smith" <richard@ibisaustralia.com>
X-Mailer: Apple Mail (2.619)
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - a2s1.a2webhosting.com
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12]
X-AntiAbuse: Sender Address Domain - pgedit.com
X-Source: 
X-Source-Args: 
X-Source-Dir: 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200412/127
X-Sequence-Number: 19747


On Dec 17, 2004, at 1:23 AM, Richard Sydney-Smith wrote:

> I expect this has been done MANY times and I wonder if a general 
> purpose trigger exists or if not then can someone point me to an 
> example set of triggers?


I'm not aware of a "general purpose" trigger for this. If you just want 
some extra trigger examples other than what is in the documentation, 
there is a test file in the distribution with quite a few:

src/test/regress/sql/plpgsql.sql

Best,

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL




From RobertD.Stewart@ky.gov Fri Dec  17 15:51:36 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 29FD73CC9C2
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 17 Dec 2004 15:51:36 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 24275-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 17 Dec 2004 15:51:21 +0000 (GMT)
Received: from agency2.state.ky.us (agency2.state.ky.us [162.114.80.12])
	by svr1.postgresql.org (Postfix) with ESMTP id 7EDF23CC09A
	for <pgsql-sql@postgresql.org>; Fri, 17 Dec 2004 15:51:24 +0000 (GMT)
Received: by agency2.state.ky.us with Internet Mail Service (5.5.2657.72)
	id <YXQ12T12>; Fri, 17 Dec 2004 10:51:24 -0500
Message-ID: <06AF099D94D33B4D9120504521D6539D1038F9AD@agency33.state.ky.us>
From: RobertD.Stewart@ky.gov
To: desoi@pgedit.com, richard@ibisaustralia.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Table History
Date: Fri, 17 Dec 2004 10:51:24 -0500
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2657.72)
Content-Type: text/plain
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.226 tagged_above=0 required=5 tests=AWL,
 NO_REAL_NAME
X-Spam-Level: 
X-Archive-Number: 200412/128
X-Sequence-Number: 19748

Here is a example of a trigger function

CREATE OR REPLACE FUNCTION public.ipinfo_trg()
  RETURNS trigger AS
'DECLARE

dhcp 	varchar:=\'DHCP\';
rtype 	varchar:=\'RAS\';

BEGIN
if NEW.ipaddress != dhcp then
	if OLD.ipaddress != dhcp then
		if OLD.atype != rtype then
			insert into vpnip(ipaddress)
values(inet(OLD.ipaddress));
		else			
			insert into rasip(ipaddress)
values(inet(OLD.ipaddress));
		end if;
	else end if;
else
	if OLD.ipaddress != dhcp then
		if OLD.atype != rtype then
			insert into vpnip(ipaddress)
values(inet(OLD.ipaddress));
		else			
			insert into rasip(ipaddress)
values(inet(OLD.ipaddress));
		end if;
	else end if;


END IF;
Return NEW;
END;
'
  LANGUAGE 'plpgsql' VOLATILE;

Here is a example of how to call the trigger function from your table
CREATE TRIGGER update_ipinfo_trg
  AFTER UPDATE
  ON public.ipinfo
  FOR EACH ROW
  EXECUTE PROCEDURE public.ipinfo_trg();

-----Original Message-----
From: John DeSoi [mailto:desoi@pgedit.com] 
Sent: Friday, December 17, 2004 10:38 AM
To: Richard Sydney-Smith
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Table History


On Dec 17, 2004, at 1:23 AM, Richard Sydney-Smith wrote:

> I expect this has been done MANY times and I wonder if a general 
> purpose trigger exists or if not then can someone point me to an 
> example set of triggers?


I'm not aware of a "general purpose" trigger for this. If you just want 
some extra trigger examples other than what is in the documentation, 
there is a test file in the distribution with quite a few:

src/test/regress/sql/plpgsql.sql

Best,

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly