触发器

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;