Postgres v 8.1.23

I have table that has 3 columns. JOB_START, JOB_END, and DURATION

DURATION is a derived metric and is just JOB_END-JOB_START.

Sometimes I will have to make updates to the JOB_END column and I want the DURATION to be recalculated on the fly.

So I created the following function and trigger that I thought would complete this function.

--BEGIN FUNCTION

CREATE FUNCTION duration_recalc() RETURNS TRIGGER AS $$

DECLARE
new_duration BigInt;
cid BigInt;
jid BigInt;
dateID BigInt;

BEGIN
cid=OLD.CLIENT_ID;
jid=OLD.job_id;
dateID=OLD.job_id;

IF (NEW.JOB_END != OLD.JOB_END) THEN
new_duration=(NEW.JOB_END - OLD.JOB_START)/60000; END IF;

UPDATE JOB_STATUS set DURATION=new_duration where CLIENT_ID=cid and JOB_ID=jid and DATE_ID=dateID; RETURN NULL; END; $$ LANGUAGE plpgsql;


--END FUNCTION

--BEGIN TRIGGER

CREATE TRIGGER duration_recalc AFTER UPDATE ON JOB_STATUS FOR EACH ROW EXECUTE PROCEDURE duration_recalc();
-- END TRIGGER

With both the function and trigger added to the table I can make updates to JOB_END and the DURATION column continues to be untouched.

Can someone tell me where I am making my mistake?

Wow, what an ancient beast. You should upgrade to a supported version as soon as possible (ideally 9.x)

You do not run UPDATEs in a trigger, simply assign the value to the new record.

So your function becomes this:You can also see this in the example in the manual: PostgreSQL: Documentation: Manuals: PostgreSQL 8.1: Trigger Procedures

And please use [code] tags in the future to format your SQL code so it's better readable.
For details see here: http://www.dbforums.com/misc.php?do=bbcode

This worked.

I did have to change the "AFTER" to "BEFORE" in my trigger as I read in another thread that in order to modify NEW you have to use BEFORE.

Thanks you so much for your insight.

Yes, of course. I didn't see that one.

Another thing:
when you finally upgrade to 9.x you can leave out the IF statement in your function because you can attach that condition to the trigger definition:

That will make things a bit more efficient because the trigger is only fired if job_end changes.

Given that trigger definition your function can be reduced to: