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;
            """
    )
)

Answer 1

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.

Answer 2

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;
            """,
    ),
)