触发器记录对表记录操作

来源：互联网发布：mac能玩暗黑三吗编辑：程序博客网时间：2024/04/27 20:16

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

创建记录日志表

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;

0 0