Thread: trigger question
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). Thanks, LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
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. -Josh Berkus
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
withtime 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.
> -Josh Berkus
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Larry, > 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). The problem is, there is no way for you to loop through all of the fields in a RECORD without knowing in advance what those fields are. There is no NEW.field(1) that I know of. And RECORD data types are not accessable in dynamic EXECUTE strings. So, two approaches: 1. do all this in C and manipulate the Postgres data libraries directly, allowing you to loop through the collection of fields in a table. Don't ask me how to do this; you'll have to take it up on PGSQL-HACKERS. 2. Save the entire old record (BEFORE update), and then the entire new record (AFTER update) to a history buffer table. Use the pgsql system tables to loop through the fields in the main table, logging each entry where the two fields are different. The two problems with approach #2 are: 1. you cannot reject updates this way, and 2. it'll be dog slow. The simpler solution is for you do decide on a permanent structure for your table, neh? -- -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
<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>