From: Henry Ortega <juandelacruz@gmail.com>
To: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Trigger on Insert to Update only newly inserted fields?
Date: Mon, 28 Aug 2006 11:53:36 -0400
Message-ID: <2bffcc330608280853x50b1034nca246a5dcc935e7@mail.gmail.com> (raw)
In-Reply-To: <20060828145737.GA5004@winnie.fuhr.org>
References: <2bffcc330608280702y16c25090i816589e20163e7e8@mail.gmail.com>
<20060828145737.GA5004@winnie.fuhr.org>
Here's what I am doing:
I have this table:
employee payrate effective
tstamp end_date (to be updated by
trigger)
jdoe 1000 04-01-2006 2006-03-10 13:39:07.614945
jdoe 1500 04-01-2006 2006-03-12 15:43:14.423325
jdoe 1555 04-16-2006 2006-03-15 12:14:15.112444
peter 500 04-1-2006 2006-03-25 08:13:35.152166
peter 900 04-16-2006 2006-03-28 09:22:14.456221
After the trigger runs, I want to have this:
employee payrate effective
tstamp end_date (to be updated by
trigger)
jdoe 1000 04-01-2006 2006-03-10 13:39:07.614945
04-15-2006
jdoe 1500 04-01-2006 2006-03-12 15:43:14.423325
04-15-2006
jdoe 1555 04-16-2006 2006-03-15 12:14:15.112444
NULL
peter 500 04-1-2006 2006-03-25
08:13:35.152166
04-15-2006
peter 900 04-16-2006 2006-03-28 09:22:14.456221
NULL
The reason some of the end_date is NULL is because it is the latest record
in
table for that particular employee.
My Trigger:
CREATE FUNCTION updated_end_date() RETURNS trigger AS '
BEGIN
update table set end_date=(select effective-1 from table t2 where
t2.employee=table.employee and t2.effective>table.effective order by
t2.effective limit 1);
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
That updates ALL of the records in the table which takes so long.
Should I be doing things like this? Or is the update query on my trigger
function so wrong?
On 8/28/06, Michael Fuhr <mike@fuhr.org> wrote:
>
> On Mon, Aug 28, 2006 at 10:02:32AM -0400, Henry Ortega wrote:
> > I have a On Insert Trigger that updates one of the columns in that same
> > table.
> >
> > Is there a way for the trigger to run only for the newly inserted
> records?
> > Instead of all records in the database?
>
> Row-level INSERT and UPDATE triggers run only for the rows being
> inserted or updated. What are you doing that suggests otherwise?
>
> > E.g.:
> > ID Start_Date End_Date
> > 001 08-01-2006
> > 002 08-02-2006
> >
> > On Insert/Update, Update End_Date=now().
> > I want that to run only on new records.or the updated
> > record. How can I do this?
>
> Row-level BEFORE triggers can modify the row they're processing --
> is that what you're looking for? Something like this?
>
> CREATE FUNCTION trigfunc() RETURNS trigger AS $$
> BEGIN
> NEW.end_date := current_date;
> RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> If that's not what you mean then please elaborate.
>
> --
> Michael Fuhr
>
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: juandelacruz@gmail.com, mike@fuhr.org
Subject: Re: Trigger on Insert to Update only newly inserted fields?
In-Reply-To: <2bffcc330608280853x50b1034nca246a5dcc935e7@mail.gmail.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