pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Table History
3+ messages / 3 participants
[nested] [flat]

* Table History
@ 2004-12-17 06:23  Richard Sydney-Smith <richard@ibisaustralia.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Richard Sydney-Smith @ 2004-12-17 06:23 UTC (permalink / raw)
  To: pgsql-sql

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
 

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Table History
@ 2004-12-17 15:37  John DeSoi <desoi@pgedit.com>
  parent: Richard Sydney-Smith <richard@ibisaustralia.com>
  0 siblings, 0 replies; 3+ messages in thread

From: John DeSoi @ 2004-12-17 15:37 UTC (permalink / raw)
  To: Richard Sydney-Smith <richard@ibisaustralia.com>; +Cc: pgsql-sql


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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Table History
@ 2004-12-17 15:51  RobertD.Stewart@ky.gov
  0 siblings, 0 replies; 3+ messages in thread

From: RobertD.Stewart@ky.gov @ 2004-12-17 15:51 UTC (permalink / raw)
  To: desoi@pgedit.com; richard@ibisaustralia.com; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2004-12-17 15:51 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-17 06:23 Table History Richard Sydney-Smith <richard@ibisaustralia.com>
2004-12-17 15:37 ` John DeSoi <desoi@pgedit.com>
2004-12-17 15:51 Re: Table History RobertD.Stewart@ky.gov

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