PLSQL - 记录某个表中新增、更改、删除的数据

应用场景：当我们需要追踪某个表中新增、更改、删除的数据时，可以在该表上建立一个触发器，将新增、更改、删除的数据，另包括DML操作类型、DML操作时间等记录到TEMP表。

--建立测试表
create table TEST1123
( EMP_ID    NUMBER,
  EMP_NAME  VARCHAR2(20),
  EMP_DESC  VARCHAR2(20));
  
--建立TEMP表用于记录测试表中插入、更新、删除的数据
create table TEST1123_TEMP as select * from TEST1123 where 1=2;
--增加两列用于记录DML类型、操作时间
alter table TEST1123_TEMP add (DML_TYPE varchar2(50), DML_TIME DATE);

--建立触发器
create or replace trigger TEST1123_TRI
 after update or insert or delete on TEST1123
 for each row
begin
   if inserting then
     insert into TEST1123_TEMP values 
          (:new.EMP_ID, :new.EMP_NAME, :new.EMP_DESC, 'INSERT', SYSDATE);
   elsif updating then
     insert into TEST1123_TEMP values 
          (:old.EMP_ID, :old.EMP_NAME, :old.EMP_DESC, 'BEFORE_UPDATE', SYSDATE);
     insert into TEST1123_TEMP values 
          (:new.EMP_ID, :new.EMP_NAME, :new.EMP_DESC, 'AFTER_UPDATE', SYSDATE);
   elsif deleting then
     insert into TEST1123_TEMP values 
          (:old.EMP_ID, :old.EMP_NAME, :old.EMP_DESC, 'DELETE', SYSDATE);
   end if;
end;

--测试：DML操作
insert into TEST1123 values(1, 'ACE', 'TEST');
update TEST1123 SET EMP_DESC= 'HELLO' where emp_id = 1;
delete from TEST1123 where emp_id = 1;

--TEMP表记录了插入的数据，更新前后的数据，删除的数据
select * from TEST1123_TEMP order by DML_TIME

补充：自动生成触发器代码的Procedure

--INPUT：表名称
--OUTPUT：建立触发器的代码
create or replace procedure autocreate_code( p_table_name IN VARCHAR2) AS
    old_column_string varchar2(32767);
    new_column_string varchar2(32767);
BEGIN
    for temp in( select column_name
                 from all_tab_columns
                where table_name = p_table_name
                 order by column_id) loop
        old_column_string := old_column_string || ':old.' || temp.column_name || ', ';
        new_column_string := new_column_string || ':new.' || temp.column_name || ', ';
    end loop;

    dbms_output.put_line('create or replace trigger ' || p_table_name || '_TRI');
    dbms_output.put_line('  after update or insert or delete on ' || p_table_name || ' for each row');
    dbms_output.put_line('begin');
    dbms_output.put_line('  if inserting then');
    dbms_output.put_line('    insert into ' || p_table_name || '_TEMP values');
    dbms_output.put_line('           (' ||  new_column_string ||  '''INSERT'', SYSDATE);');
    -----
    dbms_output.put_line('  elsif updating then');
    dbms_output.put_line('    insert into ' || p_table_name || '_TEMP values');
    dbms_output.put_line('           (' ||  old_column_string ||  '''BEFORE_UPDATE'', SYSDATE);');
    dbms_output.put_line('    insert into ' || p_table_name || '_TEMP values' );
    dbms_output.put_line('           (' ||  new_column_string ||  '''AFTER_UPDATE'', SYSDATE);');
    -----
    dbms_output.put_line('  elsif deleting then');
    dbms_output.put_line('    insert into ' || p_table_name || '_TEMP values');
    dbms_output.put_line('           (' ||  old_column_string ||  '''DELETE'', SYSDATE);');
    ----
    dbms_output.put_line('  end if;');
    dbms_output.put_line('end;'); 
END;