复制代码 代码如下:

create or replace trigger test_trigger 
  before insert or update or delete on test_table 
  for each row 
declare 
  v_id        varchar2(30); 
  v_bdlb      varchar2(1); 
  v_jgdm      VARCHAR2(12); 
  v_jgmc      VARCHAR2(60); 
  v_gajgmc    VARCHAR2(60); 
  v_gajgwsmc  VARCHAR2(30); 
  v_jz        VARCHAR2(30); 
  v_ksdwsdwmc VARCHAR2(30); 
begin 
  /* 
    插入时往历史表中存放的是新插入的数据. 
    修改时往历史表中存放的是修改后的数据. 
    删除时往历史表中存放的是删除之前的数据. 
  */ 
  select org_id_s.nextval into v_id from dual; -- 利用seq生成主键 
  v_jgdm     := :new.row_id; 
  v_jgmc     := :new.dept_name; 
  v_gajgmc   := :new.dept_name; 
  v_gajgwsmc := :new.bmjc; 
  v_jz       := substr(v_jgdm, 7, 2); 
  if \'2\' = :new.depttype then 
    v_ksdwsdwmc := \'shiju\'; 
  else 
    if \'03\' = v_jz then 
      v_ksdwsdwmc := \'zhi\'; 
    elsif \'05\' = v_jz then 
      v_ksdwsdwmc := \'xing\'; 
    elsif \'51\' = v_jz then 
      v_ksdwsdwmc := \'she\'; 
    else 
      v_ksdwsdwmc := \'qita\'; 
    end if; 
  end if; 

  if inserting then 
    v_bdlb := \'1\'; 
    insert into test_table_h 
      (id, bdlb, jgdm, jgmc, gajgmc, gajgwsmc, jz, ksdwsdwmc) 
    values 
      (v_id, 
       v_bdlb, 
       v_jgdm, 
       v_jgmc, 
       v_gajgmc, 
       v_gajgwsmc, 
       v_jz, 
       v_ksdwsdwmc); 
  elsif updating then 
    v_bdlb := \'2\'; 
    insert into test_table_h 
      (id, bdlb, jgdm, jgmc, gajgmc, gajgwsmc, jz, ksdwsdwmc) 
    values 
      (v_id, 
       v_bdlb, 
       v_jgdm, 
       v_jgmc, 
       v_gajgmc, 
       v_gajgwsmc, 
       v_jz, 
       v_ksdwsdwmc); 
  else 
    v_bdlb     := \'3\'; 
    v_jgdm     := :old.row_id; 
    v_jgmc     := :old.dept_name; 
    v_gajgmc   := :old.dept_name; 
    v_gajgwsmc := :old.bmjc; 
    v_jz       := substr(v_jgdm, 7, 2); 
    if \'2\' = :old.depttype then 
      v_ksdwsdwmc := \'shiju\'; 
    else 
      if \'03\' = v_jz then 
        v_ksdwsdwmc := \'zhi\'; 
      elsif \'05\' = v_jz then 
        v_ksdwsdwmc := \'xing\'; 
      elsif \'51\' = v_jz then 
        v_ksdwsdwmc := \'she\'; 
      else 
        v_ksdwsdwmc := \'qita\'; 
      end if; 
    end if; 
    insert into test_table_h 
      (id, bdlb, jgdm, jgmc, gajgmc, gajgwsmc, jz, ksdwsdwmc) 
    values 
      (v_id, 
       v_bdlb, 
       v_jgdm, 
       v_jgmc, 
       v_gajgmc, 
       v_gajgwsmc, 
       v_jz, 
       v_ksdwsdwmc); 
  end if; 
end;