触发器记录对表记录操作

灰太狼 2022-07-26 11:09 171阅读 0赞

这里记录满足一定条件的操作，并将操作记录下来。

创建记录日志表

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;

![SouthEast][]

[SouthEast]: /images/20220724/817dccb8c9ee4f90932635a2e5f742c4.png