From: Scott Shattuck <Scott.Shattuck@planetcad.com>
To: pgsql-sql@postgresql.org
Subject: Re: trigger question
Date: Thu, 16 May 2002 12:33:12 -0600
Message-ID: <E9086EC3AC524A4AABE0B43F2DF3ABCBA513F2@pcadntexch01.planetcad.com> (raw)
> -----Original Message-----
> From: Larry Rosenman [mailto:ler@lerctr.org]
> Sent: Thursday, May 16, 2002 11:51 AM
> To: josh@agliodbs.com
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] trigger question
>
>
> On Thu, 2002-05-16 at 12:49, Josh Berkus wrote:
> > Larry,
> >
> > > Is there a way in PL/pgSQL to have a trigger look at the
> fields in a
> > > %ROWTYPE variable to look for changes?
> > >
> > > I'm looking to be able to log the fields that are
> different between old
> > > and new in a trigger, but don't want to have to list
> each field (It
> > > changes, occasionally, but it changes). I'd also like to add a
> > > timestamp and flag field when I store the record in the
> log table.
> > >
> > > Am I just SOL, or is there a way to do this? (7.2.1, if
> it matters).
> >
> > I'm pretty sure there is a way to do what you want, and
> it's probably simpler
> > than you think.
> >
> > However, I need you to be more explicit, with examples. I
> can't quite figure
> > out what is is you're trying to do.
> >
>
> given the following table:
> CREATE TABLE "networks" (
> "netblock" cidr,
> "router" integer,
> "interface" character varying(256),
> "dest_ip" inet,
> "mis_token" character(16),
> "assigned_date" date,
> "assigned_by" character varying(256),
> "justification_now" integer,
> "justification_1yr" integer,
> "cust_asn" integer,
> "comments" character varying(2048),
> "other_reference" character varying(256),
> "parent_asn" integer,
> "status" integer NOT NULL,
> "purpose" integer,
> "last_update_by" character varying(256),
> "last_update_at" timestamp with time zone,
> "customer_reference" integer,
> "natblock" cidr
> );
>
> I want to log the changes to it in a trigger. Occasionally
> we add fields to it, so
> I'd like the trigger to know about them automatically. I
> need to log old/new for just the fields
> that change (and the netblock, which is the primary key).
>
> I realize I can build up the insert, but that seems to be the
> hard way.
>
It might not be what you're looking for but another possibility may be to
use a function to add columns to the table and have the function update the
trigger at that time. That would at least address the table/trigger
consistency issue.
ss
Scott Shattuck
Technical Pursuit Inc.
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: Scott.Shattuck@planetcad.com
Subject: Re: trigger question
In-Reply-To: <E9086EC3AC524A4AABE0B43F2DF3ABCBA513F2@pcadntexch01.planetcad.com>
* 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