pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger on Insert to Update only newly inserted fields?
5+ messages / 3 participants
[nested] [flat]
* Trigger on Insert to Update only newly inserted fields?
@ 2006-08-28 14:02 Henry Ortega <juandelacruz@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Henry Ortega @ 2006-08-28 14:02 UTC (permalink / raw)
To: pgsql-sql
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?
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?
Thank you so much.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trigger on Insert to Update only newly inserted fields?
@ 2006-08-28 14:57 Michael Fuhr <mike@fuhr.org>
parent: Henry Ortega <juandelacruz@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Fuhr @ 2006-08-28 14:57 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trigger on Insert to Update only newly inserted fields?
@ 2006-08-28 15:53 Henry Ortega <juandelacruz@gmail.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 5+ messages in thread
From: Henry Ortega @ 2006-08-28 15:53 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: 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?
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trigger on Insert to Update only newly inserted fields?
@ 2006-08-29 10:38 Michael Fuhr <mike@fuhr.org>
parent: Henry Ortega <juandelacruz@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Michael Fuhr @ 2006-08-29 10:38 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; +Cc: pgsql-sql
On Mon, Aug 28, 2006 at 11:53:36AM -0400, Henry Ortega wrote:
> 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?
You're updating the same table that has the trigger? Beware of
endless trigger recursion.
You're not restricting the UPDATE with a WHERE clause, which explains
why it updates the entire table. Maybe you meant this:
update table set end_date = (...) where employee = new.employee;
The subselect for each row also slows down the update, although you
might not be able to avoid that if requirements demand a potentially
distinct end_date for each row.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trigger on Insert to Update only newly inserted fields?
@ 2006-08-31 14:55 Aaron Bono <postgresql@aranya.com>
parent: Henry Ortega <juandelacruz@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Aaron Bono @ 2006-08-31 14:55 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-sql
On 8/28/06, Henry Ortega <juandelacruz@gmail.com> wrote:
>
> 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
>
You may want to separate this into two tables:
employee_day
employee_day_id bigserial
employee_name varchar(100)
effective_day date
effective_pay
effective_pay_id bigserial
tstamp timestamp
employee_day_id (foreign key)
Then you can calculate end_day off the employee_day table (you can do this
without splitting out the table however splitting the table means fewer
records for this query and a more normalized database):
SELECT
ed1.employee_day_id,
ed1.employee_name,
ed1.effective_day,
case ed2.effective_day
when null then null
else min(ed2.effective_day) - interval '1 day'
end
FROM employee_day ed1
LEFT OUTER JOIN employee_day ed2 ON (
ed1.employee_name = ed2.employee_name
AND
ed1.effective_day < ed2.effective_day
)
Since it appears end_date is a derived value, you can use the above query as
a view. I guess there could be performance concerns but then you could
create a materialized view for it.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-08-31 14:55 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-28 14:02 Trigger on Insert to Update only newly inserted fields? Henry Ortega <juandelacruz@gmail.com>
2006-08-28 14:57 ` Michael Fuhr <mike@fuhr.org>
2006-08-28 15:53 ` Henry Ortega <juandelacruz@gmail.com>
2006-08-29 10:38 ` Michael Fuhr <mike@fuhr.org>
2006-08-31 14:55 ` Aaron Bono <postgresql@aranya.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