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;