Re: Trigger on Insert to Update only newly inserted fields? - Mailing list pgsql-sql
| From | Henry Ortega |
|---|---|
| Subject | Re: Trigger on Insert to Update only newly inserted fields? |
| Date | |
| Msg-id | 2bffcc330608280853x50b1034nca246a5dcc935e7@mail.gmail.com Whole thread Raw |
| In response to | Re: Trigger on Insert to Update only newly inserted fields? (Michael Fuhr <mike@fuhr.org>) |
| Responses |
Re: Trigger on Insert to Update only newly inserted fields?
Re: Trigger on Insert to Update only newly inserted fields? |
| List | pgsql-sql |
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?
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