PostgreSQL OLD not working in after update statement level trigger

Question

I'm trying to update course points by the sum of course's lessons points. It is working perfectly if I do select particular course ID like this:

BEGIN   

UPDATE course
SET points = (SELECT COALESCE(SUM("lesson"."points"), 0) AS "sum_points" FROM "course" LEFT OUTER JOIN "lesson" ON ("course"."id" = "lesson"."course_id") WHERE "course"."id" = 7) 
WHERE "course"."id" = 7;
RETURN NULL;
                
END;

But not working with OLD which is the updating instance. I want to update points of whichever course is being updated.

BEGIN   

UPDATE course
SET points = (SELECT COALESCE(SUM("lesson"."points"), 0) AS "sum_points" FROM "course" LEFT OUTER JOIN "lesson" ON ("course"."id" = "lesson"."course_id") WHERE "course"."id" = OLD."course_id")
WHERE "course"."id" = OLD."course_id";
RETURN NULL;
            
END;

I'm using django-pgtriggers: https://pypi.org/project/django-pgtrigger/

@pgtrigger.register(
    pgtrigger.Trigger(
        name="add_course_point",
        level=pgtrigger.Statement,
        when=pgtrigger.After,
        operation=pgtrigger.Update,
        func=f"""
            UPDATE course
            SET points = (SELECT COALESCE(SUM("lesson"."points"), 0) AS "sum_points" FROM "course" LEFT OUTER JOIN "lesson" ON ("course"."id" = "lesson"."course_id") WHERE "course"."id" = OLD."course_id")
            WHERE "course"."id" = OLD."course_id";
            RETURN NULL;
            """
    )
)

score 1 · Answer 1 · answered Mar 30 '22 at 08:09

OLD and NEW are always NULL in case of Statement level.

Replace level=pgtrigger.Statement with level=pgtrigger.Row

@pgtrigger.register(
    pgtrigger.Trigger(
        name="add_course_point",
        level=pgtrigger.Row,
        when=pgtrigger.After,
        operation=pgtrigger.Update,
        func=f"""
            UPDATE course
            SET points = (SELECT COALESCE(SUM("lesson"."points"), 0) AS "sum_points" FROM "course" LEFT OUTER JOIN "lesson" ON ("course"."id" = "lesson"."course_id") WHERE "course"."id" = OLD."course_id")
            WHERE "course"."id" = OLD."course_id";
            RETURN NULL;
            """
    )
)

or add referencing=pgtrigger.Referencing(old='old_table_name') and then modify your function.

I have changed the statement level to row level, but actually found out that problem was another. In order to solve it, I deleted _pgtrigger_should_ignore function from pgAdmin4 and migrated again. Working perfectly now, thanks for the answer Philippe) — Jamolkhon Akhmedov, Mar 30 '22 at 12:17

score 0 · Accepted Answer · answered Mar 31 '22 at 14:56

I have registered 2 triggers. The first one is before update trigger for inserted lessons. The second is after update trigger for updated and deleted lessons. Course points are incremented or decremented by the sum of its lessons points after all.

@pgtrigger.register(
        pgtrigger.Trigger(
        name="add_course_point",
        operation=pgtrigger.Insert,
        level=pgtrigger.Row,
        when=pgtrigger.Before,
        func=f"""
            UPDATE course
            SET points = (points + NEW.points)
            WHERE "course"."id" = NEW.course_id;
            RETURN NEW;
            """,
    ),
    pgtrigger.Trigger(
        name="update_course_point",
        operation=(pgtrigger.Update | pgtrigger.Delete),
        level=pgtrigger.Row,
        when=pgtrigger.After,
        func=f"""
            UPDATE course
            SET points = (SELECT COALESCE(SUM("lesson"."points"), 0) AS "sum_points" FROM "course" LEFT OUTER JOIN "lesson" ON ("course"."id" = "lesson"."course_id") WHERE "course"."id" = OLD.course_id)
            WHERE "course"."id" = OLD.course_id;
            RETURN NULL;
            """,
    ),
)

PostgreSQL OLD not working in after update statement level trigger

2 Answers2