Suggest any structure for below process. If I update something in a table. before affecting the changes values to be stored in history table.

CodePudding user response：

Use triggers if you want to change your data on the database (e.g. add, edit, delete, ..). The feature of a trigger is to help catch that data change event, check if the data is available before it allows the data to be valid and proceed to add it to the database. The downside is that it always runs in the background when there is a data change event on the database and it is difficult to debug

CodePudding user response：

You can use the row_to_json() function in a trigger to capture the column name and value for the effected row. You need to decide if you need the full life-to-death history (insert to delete) or the or just updated values during its lifetime. The below captures life to death. (See demo).

create or replace function archieve_table_a() 
   returns trigger 
  language plpgsql 
as $$
declare 
    a_row  jsonb;    
begin 
    if tg_op = 'INSERT' then
       a_row = row_to_json(new.*);
    else 
       a_row = row_to_json(old.*);
    end if; 
    
    insert into history_a (operation, a_row) values ( tg_op, a_row );
    return null; 
end; 
$$;

create trigger a_archieve
   after delete or insert or update on table_a
     for each row 
     execute function archieve_table_a();