Oracle的触发器的练习

  --    1、用触发器来实现主键自动增长
  --    2、数据操作日志记录 emp。
create or replace trigger TRI_AUTO_HWL
  before insert on emp_hwl  
  for each row
declare
  -- local variables here
  v_addId number;--    增长后的Id
  v_curtId number;--    当前Id
begin
  select max(e.empno) into v_curtId from emp_hwl e;
  if v_curtId is null then 
    v_addId := 1;
  else
    v_addId := v_curtId + 1;  
  end if;
  select v_addId into :new.empno from dual;
  dbms_output.put_line(v_curtId ||'   ------------');
end TRI_AUTO_HWL;

CREATE OR REPLACE TRIGGER TRI_EMP_INSERT_HWL
  BEFORE INSERT OR UPDATE OR DELETE --insert update delete 触发   
ON emp_hwl
  FOR EACH ROW
DECLARE

BEGIN

  IF inserting THEN
    insert into EMP_LOG_HWL
      (autoId, addTime, oprate, tableName, EMPNO)
    values
      (SEQ_TEST.NEXTVAL, trunc(sysdate), 'insert', 'emp_hwl',:old.empno);
  ELSIF updating THEN
    insert into EMP_LOG_HWL
      (autoId, addTime, oprate, tableName, EMPNO)
    values
      (SEQ_TEST.NEXTVAL, trunc(sysdate), 'update', 'emp_hwl',:old.empno);
  ELSIF deleting THEN
    insert into EMP_LOG_HWL
      (autoId, addTime, oprate, tableName, EMPNO)
    values
      (SEQ_TEST.NEXTVAL, trunc(sysdate), 'delete', 'emp_hwl',:old.empno);
  END IF;
END TRI_EMP_INSERT_HWL;
--    测试
insert into emp_hwl
  (ENAME, JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values
  ('汉斯','SALESMAN',7902,to_date('2014/5/30','yyyy/mm/dd'),5000,1000,10);

delete emp_hwl e where e.ename = '汉斯';

 注意：触发器中不能有事务控制语句(commit/callback/savepoint)，因其不能保证事务的完整性，若加入自治事务则可以。只需PL/SQL的声明部分加PRAGMAAUTONOMOUS_TRANSACTION就可以了。