用于连接父子审计表的 Postgres SQL

Question

我们正在使用 "1 审核每个受监控表的表";但是，在我们的示例中，emp(PARENT) 表有一个子表 emp_address，也需要对其进行监控，因此我们有 emp_audit 和 emp_address_audit 表。

postgres 审计 SQL：如何连接 PARENT 和 CHILD 表以进行报告。

/* Employee table */    
create table emp (
 emp_id integer primary key,
 empnum  integer,
 empname varchar(50),
 loginid varchar(20),
 updatetime timestamp
);

/* Address table */    
create table emp_addr (
 addr_id integer primary key,
 emp_id integer, -- references table emp
 line1 varchar(30),
 line2 varchar(30),
 loginid varchar(20),
 updatetime timestamp
);

/* Audit table for emp table */    
create table emp_audit (
 operation   character(1),
 emp_id integer,
 empnum  integer,
 empname varchar(50),
 loginid varchar(20),
 updatetime timestamp,
 txid bigint
);

/* Audit table for emp_addr table */    
create table emp_addr_audit (
 operation   character(1),
 addr_id integer,
 emp_id integer,
 line1 varchar(30),
 line2 varchar(30),
 loginid varchar(20),
 updatetime timestamp,
 txid bigint
);

我们使用 hibernate(java) 来实现持久性，并且 hibernate 仅更新那些在更新操作中修改了列的表。鉴于此，我可能在 1 个 emp_audit 表的 emp_addr_audit 表中有多个（例如 5 条）记录。

每笔交易（修改）报告需要 1 行。 该报告将包含以下列

empnum、empname、line1、line2、操作（插入/删除/更新）、loginid、updatetime

让我们考虑 2 个场景来了解需要什么：

1. 在初始事务中只有 emp 属性被创建。然后在单独的事务中，创建 emp_addr 中的相应行。因此，现在，emp_audit 表中有 1 行，emp_addr_audit 表中有 1 行。该报告将有 2 行（每笔交易各一行）。
2. emp 和 emp_addr 属性都是在单个事务中创建的。这将确保 emp_audit 中有 1 行，emp_addr_audit 中有 1 行。现在，报告将只有 1 行（因为两个表行都是在单个事务中创建的）。

什么样的 SQL 可以满足上述两种情况？

更新
场景：
事务#1：我在 emp 和 emp_addr 中插入一行。这会在 emp_audit 和 emp_addr_audit 中各生成一行。（插入）
事务#2：我更新了上面的 emp' 属性。这会在 emp_audit 中产生 UPDATE 行。
事务#3：我更新了上面的 emp_addr 的属性。这会导致 emp_addr_audit 中出现 UPDATE 行。

我尝试了以下 SQL #1，它按预期返回了 3 行；

SQL #1

SELECT emp.*, addr.*
 FROM  emp_audit emp 
 FULL OUTER JOIN emp_addr addr USING(emp_id, txid);

但是，当我向 SQL 添加 where 子句时，它仅返回 2 行。丢失的行是事务 #3 的结果，其中仅 emp_addr 表行被更新，而 emp 表行未受影响。
SQL #2

SELECT emp.*, addr.*
 FROM  emp_audit emp 
        FULL OUTER JOIN emp_addr addr USING(emp_id, txid);
WHERE  emp.empnum = 20;

什么 SQL仍然能够为我的 3 个事务获取 3 行，以便我仍然可以根据 empnum 进行过滤？

谢谢你，

Answer 1

首先在审计表中添加一个额外的列txid bigint，然后修改执行审计的存储过程以调用txid_current()来存储当前事务id和审计记录。

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Create a row in emp_audit to reflect the operation performed on emp,
        -- make use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), user, txid_current(), OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), user, txid_current(), NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit SELECT 'I', now(), user, txid_current(), NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

然后，当您需要报告审计记录时，使用 emp_id 和 txid 在 2 个表之间进行外连接，以便您可以在一行上呈现同一事务中发生的 2 个单独的插入。

SELECT emp_audit.*, emp_addr_audit.*
  FROM emp_audit
  FULL OUTER JOIN ON emp_audit.emp_id = emp_addr_audit.emp_id
                 AND emp_audit.txid = emp_addr_audit.txid;