Postgresql trigger insert on another table after update and update other tables

Question

(using Postgresql 13)

I have a post table and when I soft delete it I want to also soft delete rows on comment and post_image tables using a trigger.

I have an intermediate table deleted_post where I insert the post_id and deleted_at after the soft deletion of post:

| post_id | deleted_at |

This is the trigger:

CREATE OR REPLACE FUNCTION deleted_post_update_trigger_fnc()
    RETURNS trigger AS
$$
BEGIN
-- insert into deleted_post
INSERT INTO "deleted_post" ( "post_id", "deleted_at")
VALUES(NEW."id",NEW."deleted_at");
-- delete post comments
UPDATE comment c
SET deleted_at = NEW.deleted_at
WHERE (c.post_id = NEW.id AND c.deleted_at is null);
-- delete post image
UPDATE post_image pi
SET deleted_at = NEW.deleted_at
WHERE (pi.post_id = NEW.id AND pi.deleted_at is null);
RETURN NEW;
END
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER insert_deleted_post
  AFTER UPDATE
  ON "post"
  FOR EACH ROW
  EXECUTE PROCEDURE deleted_post_update_trigger_fnc();

When I edit a post on post table I get back this error:

{
  "extensions": {
    "path": "$",
    "code": "constraint-violation"
  },
    "message": "Not-NULL violation. null value in column \"deleted_at\" of relation \"deleted_post\" violates not-null constraint"
}

and:

description: null
exec_status: "FatalError"
hint: "Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows."
message: "tuple to be updated was already modified by an operation triggered by the current command"

I cannot spot where the issue resides in the trigger.

Is this trigger safe for many transactions? Hundreds of post could be deleted from users at the same time, is there a big risk that some transactions might fail?