触发器一个案例

最新推荐文章于 2024-11-20 19:07:11 发布

原创最新推荐文章于 2024-11-20 19:07:11 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

程序专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一个使用Oracle触发器实现的数据同步机制。当特定类型的记录被插入到源表中时，该触发器会将这些记录的相关信息同步到目标表。涉及的字段包括交易详情、业务类型、日期等。

CREATE OR REPLACE TRIGGER TRI_ZS_ZH_YJSF
  AFTER INSERT ON SB_SBSJHZ
  FOR EACH ROW

when (new.yzpzzl_dm in('BDA0610009', 'BDA0610010', 'BDA0610030', 'BDA0610094','BDA0610106','BDA0610135','BDA0610137','BDA0610350','BDA0610351','BDA0610353','BDA0610354','BDA0610352'))
DECLARE
  ZSUUI VARCHAR2(25);
BEGIN
  ZSUUI := T_UUID.NEXTVAL;
  INSERT INTO zs_zh_yjsf
    (JKQX,
     YJKQX,
     YZFSRQ,
     CBSX_DM,
     CZLX_DM,
     TZLX_DM,
     DJZCLX_DM,
     DWLSGX_DM,
     HY_DM,
     ZSFS_DM,
     YSKM_DM,
     YSFPBL_DM,
     SKGK_DM,
     ZZSNSRLX_DM,
     KSSL,
     JSYJ,
     KCSE,
     SL_1,
     SE,
     JDXZ_DM,
     SKCLLX_DM,
     YZCLRQ,
     RKRQ,
     SSGLY_DM,
     SKSSSWJG_DM,
     ZGSWSKFJ_DM,
     ZSSWJG_DM,
     LRRQ,
     LRR_DM,
     XGRQ,
     XGR_DM,
     SJGSDQ,
     SJGSRQ,
     KJDJXH,
     ZSUUID,
     YZPZXH,
     YZPZMXXH,
     GLYZPZMXXH,
     DJXH,
     YZPZZL_DM,
     ZSDLFS_DM,
     NSSBRQ,
     SKZL_DM,
     SKSX_DM,
     YJSKZT_DM,
     SBFS_DM,
     SBSX_DM_1,
     ZSXM_DM,
     ZSPM_DM,
     SKSSQQ,
     SKSSQZ,
     QRMXUUID,
     ZSZM_DM,
     SYBH_1,
     BZ)
  VALUES
    (:NEW.JKQX,
     :NEW.JKQX,
     :NEW.NSSBRQ,
     '1',
     '1',
     '1',
     :NEW.DJZCLX_DM,
     NULL,
     :NEW.HY_DM,
     :NEW.ZSFS_DM,
     '070100',
     '0701',
     '0000000009',
     NULL,
     :NEW.KSSL,
     1,
     0,
     NVL(:NEW.SL_1, 0),
     :NEW.SREHJ,
     '1000',
     '1',
     :NEW.JKQX,
     :NEW.JKQX,
     :NEW.LRR_DM,
     :NEW.SWJG_DM,
     :NEW.ZSJG_DM,
     :NEW.ZSJG_DM,
     SYSDATE,
     :NEW.LRR_DM,
     SYSDATE,
     :NEW.LRR_DM,
     SUBSTR(:NEW.SWJG_DM, 1, 5),
     SYSDATE,
     :NEW.DJXH,
     ZSUUI,
     :NEW.YZPZXH,
     1,
     11,
     :NEW.DJXH,
     :NEW.YZPZZL_DM,
     '1',
     :NEW.NSSBRQ,
     '10',
     '10',
     '10',
     '0',
     '1',
     :NEW.ZSXM_DM,
     :NEW.ZSPM_DM,
     :NEW.SKSSQQ,
     :NEW.SKSSQZ,
     NULL,
     :NEW.ZSZM_DM,
     NULL,
     NULL);

  INSERT INTO zs_wsz
    (XGR_DM,
     SPUUID,
     ZSUUID,
     DZSPHM,
     DZSPMXXH,
     GLDZSPMXXH,
     PZHM,
     DJXH,
     SKSSQQ,
     SKSSQZ,
     ZSXM_DM,
     ZSPM_DM,
     DJZCLX_DM,
     HY_DM,
     SBFS_DM,
     ZSFS_DM,
     SKZL_DM,
     SKSX_DM,
     ZSDLFS_DM,
     KSSL,
     JSYJ,
     SL_1,
     SJJE,
     KJRQ,
     ZZSNSRLX_DM,
     SKJNFS_DM,
     YSKM_DM,
     YSFPBL_DM,
     SKGK_DM,
     YHHB_DM,
     YHYYWD_DM,
     YHZH,
     JDXZ_DM,
     TZLX_DM,
     CZLX_DM,
     YJKQX,
     SSGLY_DM,
     ZGSWSKFJ_DM,
     SKSSSWJG_DM,
     ZSSWJG_DM,
     BZ_1,
     BZ_2,
     LRRQ,
     LRR_DM,
     XGRQ,
     KCSE,
     KJDJXH,
     HZSPUUID,
     JKQX,
     SJGSDQ,
     SJGSRQ,
     HZRQ_1,
     PZZG,
     CKZHZHUUID,
     PZZL_DM,
     ZSZM_DM,
     SYBH_1)
  VALUES
    (:NEW.LRR_DM,
     ZSUUI,
     ZSUUI,
     T_SPHM.NEXTVAL,
     1,
     11,
     T_SPHM.NEXTVAL,
     :NEW.DJXH,
     :NEW.SKSSQQ,
     :NEW.SKSSQZ,
     :NEW.ZSXM_DM,
     :NEW.ZSPM_DM,
     :NEW.DJZCLX_DM,
     :NEW.HY_DM,
     :NEW.SBFS_DM,
     '100',
     '10',
     '16',
     '2',
     0.1000,
     1111,
     :NEW.SL_1,
     :NEW.YBTSE,
     SYSDATE,
     NULL,
     '10',
     '101060100',
     '15010035',
     '0000000009',
     '105',
     '1111',
     '109099900900',
     23,
     '1',
     1,
     :NEW.JKQX,
     :NEW.LRR_DM,
     :NEW.ZSJG_DM,
     :NEW.SWJG_DM,
     :NEW.ZSJG_DM,
     NULL,
     NULL,
     SYSDATE,
     :NEW.LRR_DM,
     SYSDATE,
     :NEW.YBTSE,
     :NEW.DJXH,
     T_SPHM.NEXTVAL,
     :NEW.JKQX,
     SUBSTR(:NEW.SWJG_DM, 1, 5),
     SYSDATE,
     SYSDATE,
     'LU shui 100',
     '111',
     '00100',
     :NEW.ZSPM_DM,
     NULL);
END TRI_ZS_ZH_YJSF;