title: How to use PostgreSql trigger and procedure to audit/restore parent-child tables
body: <p>We want to keep the editing history of some table and restore them if necessary. For example, we have following tables. We want to audit the emp table when insert /delete action performed. Besides these, when update on version field happens, we also need to save a copy of related emp_addr records to emp_addr_audit. When users want to roll back to a specif version of one emp, we need to restore the record from emp_audit and emp_addr_audit.</p>
  
  <p>I am thinking to use a trigger to do audit work and a procedure to do restore work. I know the key part is how to maintain the integrity of parent-child tables in audit and restore work. I need some advices. Thanks. </p>
  
  <pre><code> create table emp (
   emp_id integer primary key,
   version varchar(50)
  );
  
  /* Address table */    
  create table emp_addr (
   addr_id integer primary key,
   emp_id integer, -- references table emp
   line1 varchar(30),
  
  );
  
  /* Audit table for emp table */    
  create table emp_audit (
   operation   character(1),
   updatetime timestamp,
   emp_id integer,
   version varchar(50)
  
  );
  
  /* Audit table for emp_addr table */    
  create table emp_addr_audit (
   operation   character(1),
   addr_id integer,
   emp_id integer,
   line1 varchar(30),
  );
  </code></pre>