Re: trigger question

> -----Original Message-----
> From: Larry Rosenman [mailto:ler(at)lerctr(dot)org]
> Sent: Thursday, May 16, 2002 11:51 AM
> To: josh(at)agliodbs(dot)com
> Cc: pgsql-sql(at)postgresql(dot)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.