postgresql旧值和新值中的触发器

Question

如何在postgresql中编写触发器，以维护旧值、新值和表名。我有5个表，每个表都是不同的数据结构。我想在一个表中用旧值、新值和表名维护审计细节。旧值和新值包含多个json格式的列，列名和值
示例

audit_details
----------------------------------------------------------------
date_time|table_name|old_data|new_data|user|primary_key_of_table
----------------------------------------------------------------

Answer 1

我为您编写了示例触发器函数。您可以自己进行其他更改。

CREATE TABLE test.log_table_data (
id serial not null,
schema_name varchar(100) NOT NULL,
table_name varchar(100) NOT NULL,
action_date timestamp NOT NULL DEFAULT now(),
action_type varchar(10) NOT NULL,
table_id int4 NOT NULL,
old_data jsonb NULL,
new_data jsonb NULL,
CONSTRAINT log_table_data_pk PRIMARY KEY (id)
);
create or replace function test.register_as_log()
returns trigger
language plpgsql
security definer
as $function$
declare
v_old_data json;
v_new_data json;
shemaname varchar;
tablename varchar;
begin

shemaname = tg_table_schema;
tablename = tg_table_name;

/*  json_strip_nulls - removes null values  */
if (tg_op = 'UPDATE') then
v_old_data = (select json_strip_nulls(row_to_json(old.*)));
v_new_data = (select json_strip_nulls(row_to_json(new.*)));

insert into test.log_table_data (schema_name, table_name, action_type, table_id, old_data, new_data) 
values (shemaname, tablename, 'update', old.id, v_old_data, v_new_data);
return new;
end if; 

if (tg_op = 'DELETE') then
v_old_data = (select json_strip_nulls(row_to_json(old.*)));

insert into test.log_table_data (schema_name, table_name, action_type, table_id, old_data, new_data) 
values (shemaname, tablename, 'delete', old.id, v_old_data, null);
return old;
end if; 

end;
$function$
;