Re: trigger question

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