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

Question

我们正在使用 "1 audit table for each monitored Table" ;然而，在我们的例子中 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, operation(insert/delete/update), 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 中产生更新行。
交易 #3:我更新了上面的 emp_addr 的属性。这会在 emp_addr_audit 中产生更新行。

我尝试了以下 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 STILL 能够为 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;