如何创建触发器来记录多个表的插入、更新、删除的更改

Question

我的数据库中有两个表。稍后可能会增加。我想添加另一个表审计来跟踪对现有两个表的更改。我想跟踪对这些表中的任何一个进行的任何更改 AEdit 表结构为
身份证号
表_名称
字段名称
旧值
新值
修改者
Date_of_Modification

所以现在我想为两个表都有一个触发器，可以在插入、更新或删除我的任一表时触发该触发器。 当这个触发器被触发时，我想在审计中插入值。当我更新时，我想要旧值和新值。当插入时，我想要旧值作为空值，新值作为插入。当删除时，我想要旧值作为旧值现有值和删除后的新值。

Answer 1

我不太确定你的问题是什么。触发器可以使用 :OLD 和 :NEW 关键字，如下所示：

create trigger table1_trg
after insert or update or delete on table1
for each row
begin
   if :old.col1 is null and :new.col1 is not null
   or :old.col1 is not null and :new.col1 is null
   or :old.col1 != :new.col1 
   then
      insert into audit_table ...
   end if;

   -- Ditto for col2, col3, ...
end;

没有通用方法可以执行此操作，您必须为每一列编写代码。但是，您可以像这样封装逻辑：

procedure log_col_change
   ( p_table_name varchar2
   , p_column_name varchar2
   , p_old_val varchar2
   , p_new_val varchar2
   )
is
begin
   if p_old_val is null and p_new_val is not null
   or p_old_val is not null and p_new_val is null
   or p_old_val != p_new_val 
   then
      insert into audit_table ...
   end if;
end;

-- Overloaded version to handles DATE columns without losing time component
procedure log_col_change
   ( p_table_name varchar2
   , p_column_name varchar2
   , p_old_val date
   , p_new_val date
   )
is
begin
   log_col_change (p_table_name, p_column_name
                  , to_char(p_old_val,'YYYY-MM-DD HH24:MI:SS')
                  , to_char(p_new_val,'YYYY-MM-DD HH24:MI:SS')
                  );
end;

触发器是：

create trigger table1_trg
after insert or update or delete on table1
for each row
begin
   log_col_change ('MYTABLE', 'COL1', :old.col1, :new.col1);
   log_col_change ('MYTABLE', 'COL2', :old.col2, :new.col2);
   ... etc.
end;

注意 最佳实践是将过程放入包中。