如何利用系统已有功能实现和外部数据源一样的效果（法二）

 产品的外部数据源功能，无非就是实现薪酬在取数的时候，可以取任意的数据。这里通过研究发现，其实也可以通过Oracle数据库、Sqlsever数据库的触发器来实现同样的操作。这里以Oracle为例：如下图，当f_118工资项为负数的时候，有限通过f_123工资项来冲抵，然后通过f_137工资项目来冲抵。F_193,F_192为分别冲抵的金额。我们在设置公式去判断f_118<0进行冲抵的时候，逻辑复杂发现公式被循环引用了，要实现该效果可以这样处理。

（1）通过视图算出分别要冲抵的金额，冲抵行数据的主键

    如上图，附SQL：

 --视图ZZ_WADATA_TIAOZHENG

ｓｅｌｅｃｔ

pk_wa_data,

pk_psndoc,

f_118,

f_123,--表三:通讯费补贴

f_137,--表一:产量绩效及效益奖

case when f_123>0 and 0-f_118>=f_123 then 0-f_123

     when f_123>0 and 0-f_118<f_123 then f_118

else 0 end f_193,--因表二应发为负导致表三通讯费调减金额

case when f_137>0  and 0-f_118>=f_123 then f_118+f_123

else 0 end f_192,--因表二应发为负导致表一产量绩效及效益奖应调减金额

CYEAR,

CPERIOD

from wa_data where f_118<0

and  caculateflag ='Y' --已计算

and checkflag ='N' --未审核（等下有用，防止每次更新的数据量不断叠加后效率低

（2）写手动批量循环刷新语句

--循环更新

ｕｐｄａｔｅ wa_data

SET

wa_data.f_193 = (ｓｅｌｅｃｔ ZZ_WADATA_TIAOZHENG.f_193 from ZZ_WADATA_TIAOZHENG where ZZ_WADATA_TIAOZHENG.pk_wa_data = wa_data.pk_wa_data),

wa_data.f_192 = (ｓｅｌｅｃｔ ZZ_WADATA_TIAOZHENG.f_192 from ZZ_WADATA_TIAOZHENG where ZZ_WADATA_TIAOZHENG.pk_wa_data = wa_data.pk_wa_data)

WHERE EXISTS(ｓｅｌｅｃｔ 1 FROM ZZ_WADATA_TIAOZHENG WHERE ZZ_WADATA_TIAOZHENG.pk_wa_data = wa_data.pk_wa_data);

（3）建数据库定时任务（这里建到wa_periodstate 表，而非要更新的wa_data 表。点击计算后，wa_periodstate表的字段也会变动，通过wa_periodstate表来触发）

CREATE OR ｒｅｐｌａｃｅ TRIGGER WADATA_TIAOZHENG 

AFTER ｄｅｌｅｔｅ or ｉｎｓｅｒｔ or ｕｐｄａｔｅ      

on wa_periodstate                        

BEGIN

    if DELETING then

                                  ｕｐｄａｔｅ wa_data

                                  SET

                                  wa_data.f_193 = (ｓｅｌｅｃｔ ZZ_WADATA_TIAOZHENG.f_193 from ZZ_WADATA_TIAOZHENG where ZZ_WADATA_TIAOZHENG.pk_wa_data = wa_data.pk_wa_data),

                                  wa_data.f_192 = (ｓｅｌｅｃｔ ZZ_WADATA_TIAOZHENG.f_192 from ZZ_WADATA_TIAOZHENG where ZZ_WADATA_TIAOZHENG.pk_wa_data = wa_data.pk_wa_data)

                                  WHERE EXISTS(ｓｅｌｅｃｔ 1 FROM ZZ_WADATA_TIAOZHENG WHERE ZZ_WADATA_TIAOZHENG.pk_wa_data = wa_data.pk_wa_data);

    elsif INSERTING then

                                  ｕｐｄａｔｅ wa_data

                                  SET

                                  wa_data.f_193 = (ｓｅｌｅｃｔ ZZ_WADATA_TIAOZHENG.f_193 from ZZ_WADATA_TIAOZHENG where ZZ_WADATA_TIAOZHENG.pk_wa_data = wa_data.pk_wa_data),

                                  wa_data.f_192 = (ｓｅｌｅｃｔ ZZ_WADATA_TIAOZHENG.f_192 from ZZ_WADATA_TIAOZHENG where ZZ_WADATA_TIAOZHENG.pk_wa_data = wa_data.pk_wa_data)

                                  WHERE EXISTS(ｓｅｌｅｃｔ 1 FROM ZZ_WADATA_TIAOZHENG WHERE ZZ_WADATA_TIAOZHENG.pk_wa_data = wa_data.pk_wa_data);

                 elsif UPDATING then

                                  ｕｐｄａｔｅ wa_data

                                  SET

                                  wa_data.f_193 = (ｓｅｌｅｃｔ ZZ_WADATA_TIAOZHENG.f_193 from ZZ_WADATA_TIAOZHENG where ZZ_WADATA_TIAOZHENG.pk_wa_data = wa_data.pk_wa_data),

                                  wa_data.f_192 = (ｓｅｌｅｃｔ ZZ_WADATA_TIAOZHENG.f_192 from ZZ_WADATA_TIAOZHENG where ZZ_WADATA_TIAOZHENG.pk_wa_data = wa_data.pk_wa_data)

                                  WHERE EXISTS(ｓｅｌｅｃｔ 1 FROM ZZ_WADATA_TIAOZHENG WHERE ZZ_WADATA_TIAOZHENG.pk_wa_data = wa_data.pk_wa_data);

    end if; END;

（4）实现效果：薪资发放界面，点击计算，f_193与f_192字段，未设置任何公式，通过SQL脚本计算出了值）。