代码之家 › 专栏 › 技术社区 › anjanb

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

audit parent-child reporting postgresql sql

1

anjanb · 技术社区 · 14 年前

我们用的是 "1 audit table for each monitored Table" emp(PARENT) 表有一个子表 emp_address 这也需要监控,所以我们有 emp_audit 和 emp_address_audit tables .

postgresauditsql:如何连接父表和子表以进行报告。

/* 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表中有多个(比如说,5个)记录用于1个emp\u audit表。

报表需要为每个事务(修改)设置一行。报告将包含以下列

empnum,empname,line1,line2,operation(插入/删除/更新),loginid,updatetime

让我们考虑两个场景来了解需要什么:

emp 创建属性。然后在一个单独的事务中,在 emp_addr 已创建。所以,现在,我们有一排 环境管理计划审计 表和中的1行 emp_addr_audit
两者 电磁脉冲 和 emp\ U地址 属性是在单个事务中创建的。这将确保中有1行和一排 emp地址审计 . 现在,报表将只有1行(因为两个表行都是在单个事务中创建的)。

事务#1:我在emp和emp#u addr中插入一行。这将在emp\u audit和emp\u addr\u 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#u addr table行被更新,emp table行未被触及。
SQL数据库2

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

仍然是 能够为3个事务获取3行,这样我仍然可以根据empnum进行筛选?

谢谢您,

1 回复 | 直到 14 年前

1

0

Michael Barker 14 年前

首先添加一个附加列 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\u id和txid在两个表之间进行外部联接,这样您就可以在一行上显示同一事务中发生的两个独立插入。

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;