PostgresSQL : Update Certain Column in Table-B After Certain Column in Table-B is Updated

Question

I have 2 table in Postgres, let's say Category and Thread with relation one-to-many

Category

id   | title | created_at | deleted_at 
-----+-------+------------+-----------

Thread

id   | category_id | title | created_at | deleted_at 
-----+-------------+-------+------------+-----------

I want to create a trigger : if i perfom soft delete in Category (update deleted_at from null to now() ), then Thread.deleted_at should get updated too.

Here is my function

    CREATE OR REPLACE FUNCTION "thread_soft_delete"()
    RETURNS trigger
    AS $pg1$
    BEGIN
    UPDATE thread SET deleted_at = NOW()
    FROM "category"
    WHERE thread.category_id = "category".id;
    RETURN NULL;
    END;
    $pg1$
    VOLATILE
    LANGUAGE plpgsql;

And here the trigger

    CREATE TRIGGER category_delete
    AFTER UPDATE ON "category"
    FOR EACH ROW
    WHEN (OLD.deleted_at IS DISTINCT FROM NEW.deleted_at)
    EXECUTE PROCEDURE thread_soft_delete();

When i run update "category" set deleted_at = now() WHERE id = 1;, all created_at column in thread get updated. My expectation is only rows with category_id = 1 that going to update.

Please help me

Answer 1

You need not reference the category table in the trigger function. Just NEW.id() should be fine.

CREATE OR REPLACE FUNCTION "thread_soft_delete"()
RETURNS trigger
AS $pg1$
BEGIN
UPDATE thread SET deleted_at = NOW()
WHERE thread.category_id = NEW.id;
RETURN NULL;
END;
$pg1$
VOLATILE
LANGUAGE plpgsql;