oracle - 使用 oracle 触发器审计 50 列

我需要在 trigger 中创建一个 oracle 11g 来审计表。

我有一个带有 50 columns 的表，需要是 audited 。

对于将 every new insert 放入表中，我需要在 audit table (1 row) 中放入一个条目。

对于 every update ，假设我更新了 1st 2nd column ，那么它将使用它的 old value and new value 在审计中创建两个记录。

审计表的结构将是

 id        NOT NULL
 attribute NOT NULL
 OLD VALUE NOT NULL
 NEW VALUE NOT NULL
 cre_date  NOT NULL
 upd_date  NULL
 cre_time  NOT NULL
 upd_time  NULL

在 insert 的情况下，只需要填充主键(主表)即 id 和 cre_date and cre_time 并且 attribute 等于 * ，在更新的情况下，假设 colA 和 colB 正在更新，那么都需要填充。在这种情况下，两个将使用第一条记录的属性 colA 和相应的 old and new 值创建记录，对于 colB 也是如此

现在我的审计解决方案是 not very optimized ，我创建了一个 row level trigger ，它将根据其 changed (如果 -else)检查该表的每 50 列是否是 new and old value ，并且它将填充审计表.
我对我的解决方案不满意，这就是我在这里发帖的原因。
我在下面的链接中看到的另一个解决方案:
http://stackoverflow.com/questions/1421645/oracle-excluding-updates-of-one-column-for-firing-a-trigger
这在我的情况下不起作用，我为此做了一个 POC，如下所示:

create table temp12(id number);

create or replace trigger my_trigger
after update or insert on temp12
for each row
declare
  TYPE tab_col_nt IS table of varchar2(30);

  v_tab_col_nt tab_col_nt;

begin
 v_tab_col_nt := tab_col_nt('id','name');

   for r in v_tab_col_nt.first..v_tab_col_nt.last
   loop
      if updating(r) then
         insert into data_table values(1,'i am updating'||r);
      else
      insert into data_table values(2,'i am inserting'||r);
      end if;
   end loop;

 end;

如果更新它正在调用 else 部分，我不知道为什么。
这可以通过 compound trigger 实现吗

最佳答案

始终调用 else 的直接问题是因为您直接使用索引变量 r，而不是查找相关列名:

for r in v_tab_col_nt.first..v_tab_col_nt.last
loop
    if updating(v_tab_col_nt(r)) then
        insert into data_table values(1,'i am updating '||v_tab_col_nt(r));
    else
        insert into data_table values(2,'i am inserting '||v_tab_col_nt(r));
    end if;
end loop;

您也只在表创建中显示 id 列，因此当 r 是 2 时，它​​总是说它正在插入 name ，从不更新。更重要的是，如果您确实有一个 name 列并且仅针对给定的 id 更新该列，则此代码将在 id 未更改时将其显示为插入。您需要将插入/更新拆分为单独的块:

if updating then
    for r in v_tab_col_nt.first..v_tab_col_nt.last loop
        if updating(v_tab_col_nt(r)) then
            insert into data_table values(1,'i am updating '||v_tab_col_nt(r));
        end if;
    end loop;
else /* inserting */
    for r in v_tab_col_nt.first..v_tab_col_nt.last loop
        insert into data_table values(2,'i am inserting '||v_tab_col_nt(r));
    end loop;
end if;

即使该列不存在，这仍然会说它正在插入 name，但我认为这是一个错误，而且我猜您无论如何都会尝试从 user_tab_columns 填充名称列表，如果您真的想尝试使其动态化.

我同意(至少是其中一些)其他人的观点，即您最好使用一个审计表，该表复制整行而不是单个列。您的反对意见似乎是单独列出更改的列的复杂性。当您需要逐列数据时，您仍然可以通过对审计表进行逆透视来获取此信息，只需稍加工作即可。例如:

create table temp12(id number, col1 number, col2 number, col3 number);
create table temp12_audit(id number, col1 number, col2 number, col3 number,
    action char(1), when timestamp);

create or replace trigger temp12_trig
before update or insert on temp12
for each row
declare
    l_action char(1);
begin
    if inserting then
        l_action := 'I';
    else
        l_action := 'U';
    end if;

    insert into temp12_audit(id, col1, col2, col3, action, when)
    values (:new.id, :new.col1, :new.col2, :new.col3, l_action, systimestamp);
end;
/

insert into temp12(id, col1, col2, col3) values (123, 1, 2, 3);
insert into temp12(id, col1, col2, col3) values (456, 4, 5, 6);
update temp12 set col1 = 9, col2 = 8 where id = 123;
update temp12 set col1 = 7, col3 = 9 where id = 456;
update temp12 set col3 = 7 where id = 123;

select * from temp12_audit order by when;

        ID       COL1       COL2       COL3 A WHEN
---------- ---------- ---------- ---------- - -------------------------
       123          1          2          3 I 29/06/2012 15:07:47.349
       456          4          5          6 I 29/06/2012 15:07:47.357
       123          9          8          3 U 29/06/2012 15:07:47.366
       456          7          5          9 U 29/06/2012 15:07:47.369
       123          9          8          7 U 29/06/2012 15:07:47.371

因此，对于所采取的每个操作、两个插入和三个更新，您都有一个审计行。但是您希望查看更改的每一列的单独数据。

select distinct id, when,
    case
        when action = 'I' then 'Record inserted'
        when prev_value is null and value is not null
            then col || ' set to ' || value
        when prev_value is not null and value is null
            then col || ' set to null'
        else col || ' changed from ' || prev_value || ' to ' || value
    end as change
from (
    select *
    from (
        select id,
            col1, lag(col1) over (partition by id order by when) as prev_col1,
            col2, lag(col2) over (partition by id order by when) as prev_col2,
            col3, lag(col3) over (partition by id order by when) as prev_col3,
            action, when
        from temp12_audit
    )
    unpivot ((value, prev_value) for col in (
        (col1, prev_col1) as 'col1',
        (col2, prev_col2) as 'col2',
        (col3, prev_col3) as 'col3')
    )
)
where value != prev_value
    or (value is null and prev_value is not null)
    or (value is not null and prev_value is null)
order by when, id;

        ID WHEN                      CHANGE
---------- ------------------------- -------------------------
       123 29/06/2012 15:07:47.349   Record inserted
       456 29/06/2012 15:07:47.357   Record inserted
       123 29/06/2012 15:07:47.366   col1 changed from 1 to 9
       123 29/06/2012 15:07:47.366   col2 changed from 2 to 8
       456 29/06/2012 15:07:47.369   col1 changed from 4 to 7
       456 29/06/2012 15:07:47.369   col3 changed from 6 to 9
       123 29/06/2012 15:07:47.371   col3 changed from 3 to 7

五次审核记录变成七次更新；三个更新语句显示了被修改的五列。如果你会经常使用它，你可以考虑把它变成一个 View 。

所以让我们稍微分解一下。核心是这个内部选择，它使用 lag() 从该 id 的先前审计记录中获取该行的先前值:

        select id,
            col1, lag(col1) over (partition by id order by when) as prev_col1,
            col2, lag(col2) over (partition by id order by when) as prev_col2,
            col3, lag(col3) over (partition by id order by when) as prev_col3,
            action, when
        from temp12_audit

这为我们提供了一个临时 View ，其中包含所有审计表列以及用于 unpivot() 操作的滞后列，您可以使用它，因为您已将问题标记为 11g:

    select *
    from (
        ...
    )
    unpivot ((value, prev_value) for col in (
        (col1, prev_col1) as 'col1',
        (col2, prev_col2) as 'col2',
        (col3, prev_col3) as 'col3')
    )

现在我们有一个包含 id, action, when, col, value, prev_value 列的临时 View ；在这种情况下，因为我只有三列，所以审计表中的行数是其三倍。最后，外部选择过滤器仅包含值已更改的行，即 value != prev_value 所在的行(允许空值)。

select
    ...
from (
    ...
)
where value != prev_value
    or (value is null and prev_value is not null)
    or (value is not null and prev_value is null)

我使用 case 只是打印一些东西，但当然你可以对数据做任何你想做的事情。 distinct 是必需的，因为审计表中的 insert 条目也在非透视 View 中转换为三行，并且我从我的第一个 case 子句中为所有三行显示相同的文本。

关于oracle - 使用 oracle 触发器审计 50 列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11261929/