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

On 8/28/06, Henry Ortega <juandelacruz(at)gmail(dot)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
==================================================================