使用 oracle 触发器审计 50 列 [英] auditing 50 columns using oracle trigger

问题描述

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

I need to create a trigger in oracle 11g for auditing a table .

我有一个需要审计的50列的表格.

I have a table with 50 columns that need to be audited.

• 对于每个新插入到表中，我需要在审计表(1行)中放入一个条目.
• 对于每次更新，假设我更新1st 2nd column，那么它将在审计中创建两条记录，其旧值和新值.

• For every new insert into a table ,i need to put an entry in audit table (1 row).
• For every update ,suppose i update 1st 2nd column ,then it will create two record in audit with its 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和cre_time，并且attribute 等于 * ，在更新的情况下，假设 colA 和 colB 正在更新，那么都需要填充.在这种情况下，将使用第一条记录的属性创建两条记录 colA 和对应的 old and new 值，与 colB

In case of insert ,only the primary key (main table)i.e the id and cre_date and cre_time need to be populated and attribute equal to * ,in case of update ,suppose colA and colB is updating then all need to be populated.In this case two records will be created with attribute of first record colA and corresponding old and new value , and same for the colB

现在我的审计解决方案不是很优化，我创建了一个行级触发器，它将检查该表的每50列是否是根据其新旧值(if -else)是否被更改，它将填充审计表.我对我的解决方案不满意，这就是我在这里发帖的原因.我在下面的链接中看到的另一个解决方案:

Now my solution to audit is not very optimized , i have created a row level trigger ,which will check for each and every 50 columns for that table whether it is been changed or not based on its new and old value(if -else) , and it will populate the audit table . I am not satisfied with my soltuion thats why i am posting here. Another solution which i have seen in the link below :

http://stackoverflow.com/questions/1421645/oracle-exclusive-updates-of-one-column-for-firing-a-trigger

这在我的情况下不起作用，我为此做了一个 POC，如下所示:

This is not working in my case , I have done a POC for that as shown below:

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 部分，我不知道为什么.这可以通过复合触发器

In case of updating it is calling the else part i don't know why . Can this be possible through compound trigger

推荐答案

else 总是被调用的直接问题是因为你正在使用索引变量 r直接，而不是查找相关的列名:

Your immediate problem with the else always being called is because you're using your index variable r directly, rather than looking up the relevant column name:

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它没有改变.您需要将插入/更新拆分为单独的块:

You're also only showing an id column in your table creation, so when r is 2, it will always say it's inserting name, never updating. More importantly, if you did have a name column and were only updating that for a given id, this code would show the id as inserting when it hadn't changed. You need to split the insert/update into separate blocks:

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 无论如何，如果你真的想让它变得动态.

This will still say it's inserting name even if the column doesn't exist, but I assume that's a mistake, and I guess you'd be trying to populate the list of names from user_tab_columns anyway if you really want to try to make it dynamic.

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

I agree with (at least some of) the others that you'd probably be better off with an audit table that takes a copy of the whole row, rather than individual columns. Your objection seems to be the complication of individually listing which columns changed. You could still get this information, with a bit of work, by unpivoting the audit table when you need column-by-column data. For example:

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

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

So you have one audit row for each action taken, two inserts and three updates. But you want to see separate data for each column that changed.

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

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

The five audit records have turned into seven updates; the three update statements show the five columns modified. If you'll be using this a lot, you might consider making that into a view.

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

So lets break that down just a little bit. The core is this inner select, which uses lag() to get the previous value of the row, from the previous audit record for that 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

这给了我们一个临时视图，其中包含所有审计表列和滞​​后列，然后用于 unpivot() 操作，您可以使用该操作，因为您已将问题标记为 11g:

That gives us a temporary view which has all the audit tables columns plus the lag column which is then used for the unpivot() operation, which you can use as you've tagged the question as 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 列的临时视图；在这种情况下，因为我只有三列，所以审计表中的行数是其三倍.最后，外部选择过滤器仅包含值已更改的行，即其中 value != prev_value(允许空值).

Now we have a temporary view which has id, action, when, col, value, prev_value columns; in this case as I only have three columns, that has three times the number of rows in the audit table. Finally the outer select filters that view to only include the rows where the value has changed, i.e. where value != prev_value (allowing for nulls).

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 条目也在非透视视图中转换为三行，并且我从我的视图中为所有三行显示相同的文本第一个 case 子句.

I'm using case to just print something, but of course you can do whatever you want with the data. The distinct is needed because the insert entries in the audit table are also converted to three rows in the unpivoted view, and I'm showing the same text for all three from my first case clause.

这篇关于使用 oracle 触发器审计 50 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！