pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger question
14+ messages / 9 participants
[nested] [flat]
* Trigger question
@ 1999-05-10 17:38 Adam H. Pendleton <apendleton@vgsinc.com>
0 siblings, 0 replies; 14+ messages in thread
From: Adam H. Pendleton @ 1999-05-10 17:38 UTC (permalink / raw)
To: pgsql-sql@hub.org
I have a dilema, and I'm not sure what the best way to go about solving it
is. I have a table called employee_master, and a table called
employee_payroll. The primary key for both tables is the employee name. It
would be easy for me to set up a trigger that checked the master table when
inserting into the payroll table to make sure the employee exists. My
problem is this: The employee names are stored Last, First in both tables.
The data file I am parsing to insert into the payroll table contains just
the last name. Now I can make a trigger that uses a 'like' and checks the
employees division as well to ensure that they are in the employee_master,
but how do I update with just the last name? I don't want to insert, I need
to be able to update the data in the row.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Si hoc legere scis nimium eruditionis habes.
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [SQL] Trigger question
@ 1999-05-10 18:41 Michael J Davis <michael.j.davis@tvguide.com>
0 siblings, 0 replies; 14+ messages in thread
From: Michael J Davis @ 1999-05-10 18:41 UTC (permalink / raw)
To: 'Adam H. Pendleton' <apendleton@vgsinc.com>; pgsql-sql@hub.org
I would not store the last name in the employee_payroll table. I the
primary should be something like an employee_id. This would eliminate the
need to update the employee_payroll table every time an employee name
changes.
> -----Original Message-----
> From: Adam H. Pendleton [SMTP:apendleton@vgsinc.com]
> Sent: Monday, May 10, 1999 11:38 AM
> To: pgsql-sql@hub.org
> Subject: [SQL] Trigger question
>
> I have a dilema, and I'm not sure what the best way to go about solving it
> is. I have a table called employee_master, and a table called
> employee_payroll. The primary key for both tables is the employee name.
> It
> would be easy for me to set up a trigger that checked the master table
> when
> inserting into the payroll table to make sure the employee exists. My
> problem is this: The employee names are stored Last, First in both
> tables.
> The data file I am parsing to insert into the payroll table contains just
> the last name. Now I can make a trigger that uses a 'like' and checks the
> employees division as well to ensure that they are in the employee_master,
> but how do I update with just the last name? I don't want to insert, I
> need
> to be able to update the data in the row.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Si hoc legere scis nimium eruditionis habes.
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* trigger question
@ 2000-10-03 00:50 Carolyn Lu Wong <carolyn@kss.net.au>
0 siblings, 0 replies; 14+ messages in thread
From: Carolyn Lu Wong @ 2000-10-03 00:50 UTC (permalink / raw)
To: pgsql-sql
I have created a trigger when updating/inserting into a table, the row
is inserted into a log table. I get the following error when i tried to
update the table:
ERROR: Unable to locate type oid 0 in catalog
what does this mean?
^ permalink raw reply [nested|flat] 14+ messages in thread
* Trigger question
@ 2001-02-12 03:12 Dave Wedwick <news@wedwick.com>
2001-02-14 15:06 ` Re: Trigger question Dave Wedwick <news@wedwick.com>
2001-02-14 19:51 ` Re: Trigger question Jan Wieck <janwieck@Yahoo.com>
0 siblings, 2 replies; 14+ messages in thread
From: Dave Wedwick @ 2001-02-12 03:12 UTC (permalink / raw)
To: pgsql-sql
Hi!
I have a table with an int4 field called inserttime. Regardless of what
the user enters in this field, I want a trigger to put now() into it.
What's the syntax for the trigger?
Thanks!
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Trigger question
2001-02-12 03:12 Trigger question Dave Wedwick <news@wedwick.com>
@ 2001-02-14 15:06 ` Dave Wedwick <news@wedwick.com>
2001-02-14 18:04 ` Re: Re: Trigger question Josh Berkus <josh@agliodbs.com>
2001-02-14 23:20 ` Re: Trigger question Dan McGrath <dmcgrath19@home.com>
1 sibling, 2 replies; 14+ messages in thread
From: Dave Wedwick @ 2001-02-14 15:06 UTC (permalink / raw)
To: pgsql-sql
One person suggested setting now() as default.
That would work sometimes, but I have a situation where the user program is
updating the default field with 0. now() never gets into the field.
So, regardless of what the user enters, I want now() to be updated, and I
guess it would be via a trigger...
Thanks!
Dave Wedwick wrote:
> Hi!
>
> I have a table with an int4 field called inserttime. Regardless of what
> the user enters in this field, I want a trigger to put now() into it.
>
> What's the syntax for the trigger?
>
> Thanks!
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Re: Trigger question
2001-02-12 03:12 Trigger question Dave Wedwick <news@wedwick.com>
2001-02-14 15:06 ` Re: Trigger question Dave Wedwick <news@wedwick.com>
@ 2001-02-14 18:04 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 14+ messages in thread
From: Josh Berkus @ 2001-02-14 18:04 UTC (permalink / raw)
To: pgsql-sql
Dave,
Please look at the docs for creating triggers.
(http:/www.postgresql.org/docs/).
Hopefully, someone on this list with more experience with Update and
Insert triggers can give you some help in avoiding the classic problems,
such as infinite loops.
Let me ask the obvious question:
If the InsertTime field is not open to user input, why are you providing
users with a data-entry interface for this field at all? Why not hide
it, or display it read-only?
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Trigger question
2001-02-12 03:12 Trigger question Dave Wedwick <news@wedwick.com>
2001-02-14 15:06 ` Re: Trigger question Dave Wedwick <news@wedwick.com>
@ 2001-02-14 23:20 ` Dan McGrath <dmcgrath19@home.com>
1 sibling, 0 replies; 14+ messages in thread
From: Dan McGrath @ 2001-02-14 23:20 UTC (permalink / raw)
To: Dave Wedwick <news@wedwick.com>
Actually, a default of now() would be perfectly providing you created a rule to
trap insert's and updates and remove the setting for that time/date from being
inserted so as to have the db use the dfault which I think is far easier that
making a silly trigger for something so simple.
Dan
Dave Wedwick wrote:
> One person suggested setting now() as default.
>
> That would work sometimes, but I have a situation where the user program is
> updating the default field with 0. now() never gets into the field.
>
> So, regardless of what the user enters, I want now() to be updated, and I
> guess it would be via a trigger...
>
> Thanks!
>
> Dave Wedwick wrote:
>
> > Hi!
> >
> > I have a table with an int4 field called inserttime. Regardless of what
> > the user enters in this field, I want a trigger to put now() into it.
> >
> > What's the syntax for the trigger?
> >
> > Thanks!
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Trigger question
2001-02-12 03:12 Trigger question Dave Wedwick <news@wedwick.com>
@ 2001-02-14 19:51 ` Jan Wieck <janwieck@Yahoo.com>
1 sibling, 0 replies; 14+ messages in thread
From: Jan Wieck @ 2001-02-14 19:51 UTC (permalink / raw)
To: Dave Wedwick <news@wedwick.com>; +Cc: pgsql-sql
Dave Wedwick wrote:
> Hi!
>
> I have a table with an int4 field called inserttime. Regardless of what
> the user enters in this field, I want a trigger to put now() into it.
>
> What's the syntax for the trigger?
Sample:
CREATE TABLE t1 (
id serial PRIMARY KEY,
inserttime integer,
description text
);
CREATE FUNCTION t1_before_insert () RETURNS opaque AS '
BEGIN
NEW.inserttime := date_part(''epoch'', now());
RETURN NEW;
END;'
LANGUAGE 'plpgsql';
CREATE TRIGGER t1_before_insert BEFORE INSERT ON t1
FOR EACH ROW EXECUTE PROCEDURE t1_before_insert();
CREATE FUNCTION t1_before_update () RETURNS opaque AS '
BEGIN
NEW.inserttime := OLD.inserttime;
RETURN NEW;
END;'
LANGUAGE 'plpgsql';
CREATE TRIGGER t1_before_update BEFORE UPDATE ON t1
FOR EACH ROW EXECUTE PROCEDURE t1_before_update();
These two triggers ensure that the field 'inserttime' is set
to the number of seconds since Jan. 1st 1970 on INSERT and
will never be changed after. Close enough to what you want?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 14+ messages in thread
* Trigger question
@ 2001-02-12 03:19 Dave Wedwick <news@wedwick.com>
0 siblings, 0 replies; 14+ messages in thread
From: Dave Wedwick @ 2001-02-12 03:19 UTC (permalink / raw)
To: pgsql-sql
Hi!
I have a table with an int4 field called inserttime. Regardless of what
the user enters in this field, I want a trigger to put now() into it.
What's the syntax for the trigger?
Thanks!
^ permalink raw reply [nested|flat] 14+ messages in thread
* trigger question
@ 2002-05-16 17:38 Larry Rosenman <ler@lerctr.org>
2002-05-16 17:49 ` Re: trigger question Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 14+ messages in thread
From: Larry Rosenman @ 2002-05-16 17:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: trigger question
2002-05-16 17:38 trigger question Larry Rosenman <ler@lerctr.org>
@ 2002-05-16 17:49 ` Josh Berkus <josh@agliodbs.com>
2002-05-16 17:51 ` Re: trigger question Larry Rosenman <ler@lerctr.org>
0 siblings, 1 reply; 14+ messages in thread
From: Josh Berkus @ 2002-05-16 17:49 UTC (permalink / raw)
To: Larry Rosenman <ler@lerctr.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: trigger question
2002-05-16 17:38 trigger question Larry Rosenman <ler@lerctr.org>
2002-05-16 17:49 ` Re: trigger question Josh Berkus <josh@agliodbs.com>
@ 2002-05-16 17:51 ` Larry Rosenman <ler@lerctr.org>
2002-05-16 18:05 ` Re: trigger question Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 14+ messages in thread
From: Larry Rosenman @ 2002-05-16 17:51 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
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.
> -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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: trigger question
2002-05-16 17:38 trigger question Larry Rosenman <ler@lerctr.org>
2002-05-16 17:49 ` Re: trigger question Josh Berkus <josh@agliodbs.com>
2002-05-16 17:51 ` Re: trigger question Larry Rosenman <ler@lerctr.org>
@ 2002-05-16 18:05 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 14+ messages in thread
From: Josh Berkus @ 2002-05-16 18:05 UTC (permalink / raw)
To: Larry Rosenman <ler@lerctr.org>; +Cc: pgsql-sql
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 information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: trigger question
@ 2002-05-16 18:33 Scott Shattuck <Scott.Shattuck@planetcad.com>
0 siblings, 0 replies; 14+ messages in thread
From: Scott Shattuck @ 2002-05-16 18:33 UTC (permalink / raw)
To: pgsql-sql
> -----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.
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2002-05-16 18:33 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-05-10 17:38 Trigger question Adam H. Pendleton <apendleton@vgsinc.com>
1999-05-10 18:41 RE: [SQL] Trigger question Michael J Davis <michael.j.davis@tvguide.com>
2000-10-03 00:50 trigger question Carolyn Lu Wong <carolyn@kss.net.au>
2001-02-12 03:12 Trigger question Dave Wedwick <news@wedwick.com>
2001-02-14 15:06 ` Dave Wedwick <news@wedwick.com>
2001-02-14 18:04 ` Josh Berkus <josh@agliodbs.com>
2001-02-14 23:20 ` Dan McGrath <dmcgrath19@home.com>
2001-02-14 19:51 ` Jan Wieck <janwieck@Yahoo.com>
2001-02-12 03:19 Trigger question Dave Wedwick <news@wedwick.com>
2002-05-16 17:38 trigger question Larry Rosenman <ler@lerctr.org>
2002-05-16 17:49 ` Re: trigger question Josh Berkus <josh@agliodbs.com>
2002-05-16 17:51 ` Re: trigger question Larry Rosenman <ler@lerctr.org>
2002-05-16 18:05 ` Re: trigger question Josh Berkus <josh@agliodbs.com>
2002-05-16 18:33 Re: trigger question Scott Shattuck <Scott.Shattuck@planetcad.com>
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