觸發器記錄對表記錄操作

這裡記錄滿足一定條件的操作，并将操作記錄下來。

建立記錄日志表

create table emp_trace_log(
   empno number, -- 主鍵
   column_name varchar2(20), -- 被修改列名
   old_value varchar2(200),  -- 修改前舊值
   new_value varchar2(200),  -- 修改後新值
   update_date date default sysdate -- 修改時間，預設系統目前時間
);
           

建立觸發器

create or replace trigger tr_up_ck
  -- 删除或修改操作前
  before delete or update on scott.emp
  FOR EACH ROW --說明建立的是行級觸發器 
  -- 觸發條件，comm列值非空時
  when (old.comm is not null)
BEGIN
  case
    -- 修改列資料
    when updating('ename') then
      if :old.ename <> :new.ename then
        insert into emp_trace_log
          (empno, column_name, old_value, new_value)
        values
          (:old.empno, 'ENAME', :old.ename, :new.ename);
      end if;
    when updating('job') then
      if :old.job <> :new.job then
        insert into emp_trace_log
          (empno, column_name, old_value, new_value)
        values
          (:old.empno, 'JOB', :old.job, :new.job);
      end if;
    when updating('mgr') then
      if :old.mgr <> :new.mgr then
        insert into emp_trace_log
          (empno, column_name, old_value, new_value)
        values
          (:old.empno, 'MGR', :old.mgr, :new.mgr);
      
      end if;
    when updating('sal') then
      if :old.sal <> :new.sal then
        insert into emp_trace_log
          (empno, column_name, old_value, new_value)
        values
          (:old.empno, 'SAL', :old.sal, :new.sal);
      
      end if;
    when updating('comm') then
      if :old.comm <> :new.comm then
        insert into emp_trace_log
          (empno, column_name, old_value, new_value)
        values
          (:old.empno, 'COMM', :old.comm, :new.comm);
      
      end if;
    -- 删除行資料
    when deleting then
      insert into emp_trace_log
        (empno, column_name, old_value, new_value)
      values
        (:old.empno, 'EMPNO', :old.empno, :new.empno);
      insert into emp_trace_log
        (empno, column_name, old_value, new_value)
      values
        (:old.empno, 'ENAME', :old.ename, :new.ename);
      insert into emp_trace_log
        (empno, column_name, old_value, new_value)
      values
        (:old.empno, 'JOB', :old.job, :new.job);
      insert into emp_trace_log
        (empno, column_name, old_value, new_value)
      values
        (:old.empno, 'MGR', :old.mgr, :new.mgr);
      insert into emp_trace_log
        (empno, column_name, old_value, new_value)
      values
        (:old.empno, 'HIREDATE', :old.hiredate, :new.hiredate);
      insert into emp_trace_log
        (empno, column_name, old_value, new_value)
      values
        (:old.empno, 'SAL', :old.sal, :new.sal);
      insert into emp_trace_log
        (empno, column_name, old_value, new_value)
      values
        (:old.empno, 'COMM', :old.comm, :new.comm);
      insert into emp_trace_log
        (empno, column_name, old_value, new_value)
      values
        (:old.empno, 'DEPTNO', :old.deptno, :new.deptno);
  end case;

END;
/
           

測試

delete from emp where empno = 7844;