Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: Trigger not Firing

    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?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Quote Originally Posted by afiaccone
    Postgres v 8.1.23
    Wow, what an ancient beast. You should upgrade to a supported version as soon as possible (ideally 9.x)

    Quote Originally Posted by afiaccone
    UPDATE JOB_STATUS set DURATION=new_duration where CLIENT_ID=cid and JOB_ID=jid and DATE_ID=dateID; RETURN NULL; END;
    You do not run UPDATEs in a trigger, simply assign the value to the new record.

    So your function becomes this:
    Code:
    CREATE FUNCTION duration_recalc() RETURNS TRIGGER AS $$
    BEGIN
        IF (NEW.JOB_END != OLD.JOB_END) THEN
            new.duration := (NEW.JOB_END - OLD.JOB_START)/60000; 
        END IF;
    
        RETURN new; 
    END; $$ 
    LANGUAGE plpgsql;
    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

  3. #3
    Join Date
    Oct 2011
    Posts
    2
    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.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Quote Originally Posted by afiaccone View Post
    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.
    Yes, of course. I didn't see that one.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    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:

    Code:
    CREATE TRIGGER duration_recalc 
       BEFORE UPDATE ON JOB_STATUS 
       FOR EACH ROW 
       WHEN (OLD.JOB_END IS DISTINCT FROM NEW.JOB_END)
       EXECUTE PROCEDURE duration_recalc();
    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:

    Code:
    CREATE FUNCTION duration_recalc() RETURNS TRIGGER AS $$
    BEGIN
        new.duration := (NEW.JOB_END - OLD.JOB_START)/60000; 
        RETURN new; 
    END; $$ 
    LANGUAGE plpgsql;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •