Hi Guys,
i have Access FE tables linked with DSN connection to Postgresql on windows system.
IT is ok but i am adding now new, history table where i want to know which user inserted or deleted data.
I am creating history table like here:
https://www.cybertec-postgresql.com/...in-postgresql/
And now how to check which user was inputing data?
I can imagine that i can create log form in Access and have login variable to pass into postgresql BE
but i do not know if it would be simple
Maybe i should write Stored procedure to invoke trigger?
In BE in postgresql i will have trigger like that:
Code:
CREATE FUNCTION change_trigger() RETURNS trigger AS $$
BEGIN
IF TG_OP = 'INSERT'
THEN
INSERT INTO logging.t_history (tabname, schemaname, operation, new_val)
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW));
RETURN NEW;
ELSIF TG_OP = 'UPDATE'
THEN
INSERT INTO logging.t_history (tabname, schemaname, operation, new_val, old_val)
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP,
row_to_json(NEW), row_to_json(OLD));
RETURN NEW;
ELSIF TG_OP = 'DELETE'
THEN
INSERT INTO logging.t_history (tabname, schemaname, operation, old_val)
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD));
RETURN OLD;
END IF;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;
so i have to pass variable to postgresql somehow and use this within trigger.
Please help,
any tips will be welcome.
Best
Jacek