How to properly emulate statement level triggers with access to data in postgres

I am using PostgreSQL as my database for a project at work. We use triggers in quite a few places to either maintain computed columns, or tables that essentially act as a materialized view.

All this worked just fine when simply utilizing row level triggers to keep all this in sync. However when we wrote scripts to periodically import our customers data into the database, we ran into issues with either performance or problems with number of locks in a single transaction.

To alleviate this I wanted to create a statement-level trigger with access to the modified rows (inserted, updated or deleted). However as this is not possible I instead created a BEFORE statement-level trigger that would create a temporary table. Then an AFTER row-level trigger that would insert the changed data into the temporary table. At last an AFTER statement-level trigger that would read the changes and perform necessary updates, and then drop the temporary table.

All this works just fine, assuming that within the triggers, no one would re-trigger the same flow again (as the temporary table would then already exist).

However I then learned that when using foreign key constraints with ON DELETE SET NULL, it is simply implemented with a system trigger that sets the column to NULL. This of course is not a problem at all, except for the fact that when you have several foreign key constraints like this on a single table, all referencing the same table (let's just call this files). When deleting a row from the files table, all these system level triggers to handle the ON DELETE SET NULL clause all fire at the same time, that is in parallel. Which presents a serious issue for me.

How would I go about implementing something like this? Here is a short SQL script to illustrate the problem:

CREATE TABLE files (
    id serial PRIMARY KEY,
    "name" TEXT NOT NULL
);

CREATE TABLE profiles (
    id serial PRIMARY KEY,
    NAME TEXT NOT NULL,
    cv_file_id INT REFERENCES files(id) ON DELETE SET NULL,
    photo_file_id INT REFERENCES files(id) ON DELETE SET NULL
);

CREATE TABLE profile_audit (
    profile_id INT NOT NULL,
    modified_at timestamptz NOT NULL
);

CREATE FUNCTION pre_stmt_create_temp_table()
RETURNS TRIGGER
AS $$
BEGIN
    CREATE TEMPORARY TABLE tmp_modified_profiles (
        id INT NOT NULL
    ) ON COMMIT DROP;
    RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

CREATE FUNCTION insert_modified_profile_to_temp_table()
RETURNS TRIGGER
AS $$
BEGIN
    INSERT INTO tmp_modified_profiles(id) VALUES (NEW.id);
    RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

CREATE FUNCTION post_stmt_insert_rows_and_drop_temp_table()
RETURNS TRIGGER
AS $$
BEGIN
    INSERT INTO profile_audit (id, modified_at)
    SELECT t.id, CURRENT_TIMESTAMP FROM tmp_modified_profiles t;

    DROP TABLE tmp_modified_profiles;

    RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER tr_create_working_table BEFORE UPDATE ON profiles FOR EACH STATEMENT EXECUTE PROCEDURE pre_stmt_create_temp_table();
CREATE TRIGGER tr_insert_row_to_working_table AFTER UPDATE ON profiles FOR EACH ROW EXECUTE PROCEDURE insert_modified_profile_to_temp_table();
CREATE TRIGGER tr_insert_modified_rows_and_drop_working_table AFTER UPDATE ON profiles FOR EACH STATEMENT EXECUTE PROCEDURE post_stmt_insert_rows_and_drop_temp_table();

INSERT INTO files ("name") VALUES ('photo.jpg'), ('my_cv.pdf');

INSERT INTO profiles ("name") VALUES ('John Doe');

DELETE FROM files WHERE "name" = 'photo.jpg';