Re: Trigger on Insert to Update only newly inserted fields?

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(at)fuhr(dot)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
>