Pfthb

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

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;