Search code examples
javapostgresqljpajakarta-eepsql

How should i pass a userId(or other parameter) to PSQL trigger AFTER DELETE?

I want to pass a parameter into a trigger function, which invokes AFTER DELETE of entity record in my database. Is there any way to do it on application-level? Is there a solution using "SET" operation? Or maybe I can add a parameter to the same transaction, which is used on delete operation?

Solution

  • You have a couple of choices here.
    1. You can set the application_name environment variable to the user name, and then retrieve it in the trigger. 2. You can expand the table by a column, and send the user name to the database in the insert/update/delete statements.

    SET application_name = 'user name';

-- in the trigger:

SELECT application_name FROM pg_stat_activity WHERE pid = pg_backend_pid();

    or

    ALTER TABLE t ADD COLUMN user_name text;

-- and in the trigger (depending on context):

my_user := NEW.user_name;
or
my_user := OLD.user_name;