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

I have a On Insert Trigger that updates one of the columns in that same table.<br /><br />Is there a way for the
triggerto run only for the newly inserted records? <br />Instead of all records in the database?<br /><br />E.g.:<br
/>ID     Start_Date     End_Date <br />001   08-01-2006    <br />002   08-02-2006<br /><br /><br />On Insert/Update,
UpdateEnd_Date=now().<br />I want that to run only on new records.or the updated<br />record. How can I do this?<br
/><br/>Thank you so much.<br /> 

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

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 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

On 8/28/06, Henry Ortega <juandelacruz@gmail.com> wrote:
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
==================================================================