我们使用的是"1 audit table for each monitored Table";但是,在我们的例子中emp(PARENT)表有一个子表emp_address,它也需要监视,所以我们有emp_auditemp_address_audit tables
SQL审核:如何为报表目的加入父表和子表。

/* 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只更新在更新操作中修改了列的表。鉴于此,我可能在empôu addrôu audit表中为1个empôu audit表有多个(比如5个)记录。
每个事务(修改)需要一行报表。
报告将包含以下列
empnum,empname,第1行,第2行,操作(插入/删除/更新),loginid,updatetime
让我们考虑两个场景来了解需要什么:
在初始事务中,只创建emp属性。然后在单独的事务中,创建emp_addr中的相应行。现在,我们在emp_audit表中有一行,在emp_addr_audit表中有一行。报表将有两行(每个事务一行)。
empemp_addr属性都是在单个事务中创建的。这将确保emp_audit中有一行,emp_addr_audit中有一行。现在,报表将只有一行(因为两个表行都是在一个事务中创建的)。
什么样的SQL可以满足上述两种情况?
更新
脚本:
事务处理1:我在emp和emp地址中插入一行。这将导致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仍然能够为3个事务获取3行,以便我仍然可以根据empnum进行筛选?
谢谢您,

最佳答案

首先在审计表中添加一个额外的列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;

然后,当需要报告审核记录时,使用EMPHID和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;

08-26 17:46
查看更多