我们使用的是"1 audit table for each monitored Table";但是,在我们的例子中emp(PARENT)
表有一个子表emp_address
,它也需要监视,所以我们有emp_audit
和emp_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
表中有一行。报表将有两行(每个事务一行)。emp
和emp_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;