使用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 columns的表，该表必须是audited.

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

• 对于every new insert到表中，我需要在audit table (1 row)中放置一个条目.
• 对于every update，假设我更新了1st 2nd column，则它将使用old value and new value创建两个审核记录.

• 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 and 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

现在我的审计解决方案是not very optimized，我创建了一个row level trigger，它将根据该表的new and old value检查该表的每50列是否为changed(如果-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-excluding-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部分，我不知道为什么. 是否可以通过compound trigger

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

这为我们提供了一个临时视图，其中包含所有审核表列以及滞后列，该滞后列随后用于

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屋！