oracle 触发器 对某张表执行删除和更新操作后记录动作
create or replace trigger emp_budbefore update or delete on empfor each rowdeclare v_action varchar2(50); v_log varchar2(500) := '';begin if :new.empno is not null then begin v_log := v_log || '操作' || :new.empno ; if :new.ename <> :old.ename then v_log := v_log || ' ename 由' || :old.ename || '更改为' || :new.ename; end if; if :new.job <> :old.job then v_log := v_log || ' job 由' || :old.job || '更改为' || :new.job; end if; if :new.mgr <> :old.mgr then v_log := v_log || ' mgr 由' || :old.mgr || '更改为' || :new.mgr; end if; if :new.hiredate <> :old.hiredate then v_log := v_log || ' hiredate 由' || :old.hiredate || '更改为' || :new.hiredate; end if; if :new.sal <> :old.sal then v_log := v_log || ' sal 由' || :old.sal || '更改为' || :new.sal; end if; if :new.comm <> :old.comm then v_log := v_log || ' comm 由' || :old.comm || '更改为' || :new.comm; end if; if :new.deptno <> :old.deptno then v_log := v_log || ' deptno 由' || :old.deptno || '更改为' || :new.deptno; end if; if UPDATING then v_action := 'update'; elsif deleting then v_action := 'delete'; end if; insert into emp_log (id, action, time, log, actor) values (EMPLOG_SEQ.NEXTVAL, v_action, sysdate, v_log, USER); /*pkg_emp.v_emp_obj.empno := :new.empno; select * into pkg_emp.v_emp_obj from emp where empno = pkg_emp.v_emp_obj.empno; dbms_output.put_line ( 'ename = ' || pkg_emp.v_emp_obj.ename || ' job = ' || pkg_emp.v_emp_obj.job );*/ exception when others then dbms_output.put_line(SQLCODE || '-before-' || SQLERRM); end; else pkg_emp.v_emp_obj.empno := 0; end if;end;
?例子： update emp where empno=7369；
注意 ： 本例子中一定要有empno才可以
