触发器

create or replace trigger gxh_value_change_trigger --创建或替换 触发器
after insert on gams_assetcard --触发时机在插入数据之后
for each row --每行都触发 （默认的数据访问关键字 NEW 新数据 OLD 旧数据 例如： NEW.CARDSTATE）
begin --开始（begin 和 end 之间访问需要加':' 例如： :NEW.CARDSTATE）
case when inserting then --条件判断开始 当正在执行插入时
  case when (:NEW.CARDSTATE = '00' AND :NEW.YEWXLH = 1) then --第一个条件判断
       insert into gxh_value_change (RECID, OBJECTID, YEWXLH, BILLTIME, CARDSTATE, JIAZOLD, JIAZNEW, JIAZZJ) values (dna.new_recid, :NEW.OBJECTID, :NEW.YEWXLH, :NEW.JIZRQ, :NEW.CARDSTATE, 0, :NEW.JIAZ, :NEW.JIAZ);
  when (:NEW.CARDSTATE = '00' AND :NEW.YEWXLH > 1) then --其他条件判断
       insert into gxh_value_change (RECID, OBJECTID, YEWXLH, BILLTIME, CARDSTATE, JIAZOLD, JIAZNEW, JIAZZJ) values (dna.new_recid, :NEW.OBJECTID, :NEW.YEWXLH, :NEW.JIZRQ, :NEW.CARDSTATE, (select c.jiaz from gams_assetcard c where c.objectid = :NEW.OBJECTID and c.yewxlh = :NEW.YEWXLH - 1), :NEW.JIAZ, :NEW.JIAZ);
  end case; --条件判断结束
end case; --条件判断结束
end; --结束

--注意访问变量的方式 begin 和 end 之间 需要加冒号；begin 和 end 之外 则不需要加；
-- 多个条件判断 用 case when （）then 'sql语句'when () then 'sql语句' end case;



修改业务后的触发器

create or replace trigger gxh_value_change_trigger
after insert or update on gams_assetcard
for each row
declare

    tem_value_old number:=0;
    tem_orgunit_code varchar2(100);
    tem_orgunit_name varchar2(200);
    tem_assetclass_code varchar2(100);
    tem_assetclass_name varchar2(200);
    tem_assetclass_gb_code varchar2(100);
    tem_assetclass_gb_name varchar2(200);
    pragma autonomous_transaction;
   
begin
  select g.stdcode,g.stdname into tem_assetclass_gb_code,tem_assetclass_gb_name from gams_jc_assetsort_gb g where g.recid = :NEW.SORTGBID;
  select o.stdcode,o.stdname into tem_orgunit_code,tem_orgunit_name from md_org o where o.recid = :NEW.ORGUNIT ;
  select a.stdcode,a.stdname into tem_assetclass_code,tem_assetclass_name from gams_jc_assetclass a where a.recid = :NEW.ZICFLID;
 
  case
    when updating then
     
      case
        when (:OLD.AUDITSTATE = 1 AND :NEW.AUDITSTATE = 2 and :OLD.CARDSTATE = '00' and :OLD.YEWXLH = 1 and :OLD.ISLASTEST = 1) then
           insert into zczjk.gxh_value_change (RECID, OBJECTID, YEWXLH, YEWLX, BILLTIME, BILLCODE, ORGUNIT, CARDSTATE, JIAZOLD, JIAZNEW,JIAZZJ,CARDRECID,ZICMC,DANWMC,DANWBM,ZICFLBM,ZICFLMC,ZICDLBM,ZICDLMC) values (dna.new_recid, :NEW.OBJECTID, :NEW.YEWXLH, '00', :NEW.JIZRQ, :NEW.BILLCODE, :NEW.ORGUNIT, :NEW.CARDSTATE, 0, :NEW.JIAZ, :NEW.JIAZ,:NEW.RECID,:NEW.ZICMC,tem_orgunit_code,tem_orgunit_name,tem_assetclass_code,tem_assetclass_name,tem_assetclass_gb_code,tem_assetclass_gb_name);    
           commit;
        else
           commit;
      end case;
   
    when inserting then
     
      case
        when (:NEW.AUDITSTATE = 2 and :NEW.CARDSTATE = '00' and :NEW.YEWXLH > 1) then
         select c.jiaz into tem_value_old from gams_assetcard c where c.objectid = :NEW.OBJECTID and c.yewxlh = :NEW.YEWXLH - 1 ;
         insert into zczjk.gxh_value_change (RECID, OBJECTID, YEWXLH, YEWLX, BILLTIME, BILLCODE, ORGUNIT, CARDSTATE, JIAZOLD, JIAZNEW, JIAZZJ,CARDRECID,ZICMC,DANWMC,DANWBM,ZICFLBM,ZICFLMC,ZICDLBM,ZICDLMC) values (dna.new_recid, :NEW.OBJECTID, :NEW.YEWXLH, '10', :NEW.JIZRQ, :NEW.BILLCODE, :NEW.ORGUNIT, :NEW.CARDSTATE, tem_value_old, :NEW.JIAZ, :NEW.JIAZ - tem_value_old,:NEW.RECID,:NEW.ZICMC,tem_orgunit_code,tem_orgunit_name,tem_assetclass_code,tem_assetclass_name,tem_assetclass_gb_code,tem_assetclass_gb_name);
         commit;
        when (:NEW.AUDITSTATE = 2 and :NEW.CARDSTATE = '10') then
          insert into zczjk.gxh_value_change (RECID, OBJECTID, YEWXLH, YEWLX, BILLTIME, BILLCODE, ORGUNIT, CARDSTATE, JIAZOLD, JIAZNEW, JIAZZJ,CARDRECID,ZICMC,DANWMC,DANWBM,ZICFLBM,ZICFLMC,ZICDLBM,ZICDLMC) values (dna.new_recid, :NEW.OBJECTID, :NEW.YEWXLH, '20', :NEW.JIZRQ, :NEW.BILLCODE, :NEW.ORGUNIT, :NEW.CARDSTATE, :NEW.JIAZ, 0, 0 - :NEW.JIAZ,:NEW.RECID,:NEW.ZICMC,tem_orgunit_code,tem_orgunit_name,tem_assetclass_code,tem_assetclass_name,tem_assetclass_gb_code,tem_assetclass_gb_name);
          commit;
        when (:NEW.AUDITSTATE = 2 and :NEW.CARDSTATE = '13') then
          insert into  zczjk.gxh_value_change (RECID, OBJECTID, YEWXLH, YEWLX, BILLTIME, BILLCODE, ORGUNIT, CARDSTATE, JIAZOLD, JIAZNEW, JIAZZJ,CARDRECID,ZICMC,DANWMC,DANWBM,ZICFLBM,ZICFLMC,ZICDLBM,ZICDLMC) values (dna.new_recid, :NEW.OBJECTID, :NEW.YEWXLH, '30', :NEW.JIZRQ, :NEW.BILLCODE, :NEW.ORGUNIT, :NEW.CARDSTATE, :NEW.JIAZ, 0, 0 - :NEW.JIAZ,:NEW.RECID,:NEW.ZICMC,tem_orgunit_code,tem_orgunit_name,tem_assetclass_code,tem_assetclass_name,tem_assetclass_gb_code,tem_assetclass_gb_name);
          commit;
        else
          commit;  
      end case; 
     
    else
         commit ;
         
  end case;
end gxh_value_change_trigger;


---结构

case
  when 条件 then
    case
      when 条件 then
     
      when 条件 then

      else

    end case;
  when 条件 then
    case
      when 条件 then
     
      when 条件 then

      else

    end case;
  else

end case;