create or replace trigger emp_bud
before update or delete on emp
for each row
declare
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才可以