cre_trigger.sql

公务员管理系统

Create or replace trigger dw_xx
  before update  on dw_xx
  for each row
BEGIN
			if :old.dwmc <> :new.dwmc and :old.dwmc is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(lpad(seq_bgid.nextval,20,'0'),:new.dwid,:old.dwmc,:new.dwlb,'dwmc','单位名称',:old.dwmc,:new.dwmc,:new.xgr,:new.xgrq);
			end if;
			if :old.dwxz <> :new.dwxz and :old.dwxz is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'dwxz','单位性质',dp_sys.f_get_mc_by_dm('1002',:old.dwxz),dp_sys.f_get_mc_by_dm('1002',:new.dwxz),:new.xgr,:new.xgrq);
			end if;
			if :old.swdjh <> :new.swdjh and :old.swdjh is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'swdjh','税务登记证号',:old.swdjh,:new.swdjh,:new.xgr,:new.xgrq);
			end if;
			if :old.dwfrm <> :new.dwfrm and :old.dwfrm is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'dwfrm','单位法人码',:old.dwfrm,:new.dwfrm,:new.xgr,:new.xgrq);
			end if;
			if :old.sjdwfrm <> :new.sjdwfrm and :old.sjdwfrm is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'sjdwfrm','上级单位法人码',:old.sjdwfrm,:new.sjdwfrm,:new.xgr,:new.xgrq);
			end if;
			if :old.sjdwmc <> :new.sjdwmc and :old.sjdwmc is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'sjdwmc','上级单位法名称',:old.sjdwmc,:new.sjdwmc,:new.xgr,:new.xgrq);
			end if;
			if :old.sbdjh <> :new.sbdjh and :old.sbdjh is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'sbdjh','社会保险登记号',:old.sbdjh,:new.sbdjh,:new.xgr,:new.xgrq);
			end if;
			if :old.yyzzh <> :new.yyzzh and :old.yyzzh is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'yyzzh','营业执照号',:old.yyzzh,:new.yyzzh,:new.xgr,:new.xgrq);
			end if;
			if :old.dbr <> :new.dbr and :old.dbr is not null  then
				if :old.dwlb = '01' then
					insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
					values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'dbr','法定代表人',:old.dbr,:new.dbr,:new.xgr,:new.xgrq);
				elsif :old.dwlb = '02' then
					insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
					values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'dbr','经营者',:old.dbr,:new.dbr,:new.xgr,:new.xgrq);
				elsif :old.dwlb = '03' then
					insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
					values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'dbr','负责人',:old.dbr,:new.dbr,:new.xgr,:new.xgrq);
				end if;
			end if;
			if :old.bzhm <> :new.bzhm and :old.bzhm is not null  then
				if :old.dwlb = '01' then
					insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
					values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'bzhm','法人身份证号',:old.bzhm,:new.bzhm,:new.xgr,:new.xgrq);
				elsif :old.dwlb = '02' then
					insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
					values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'bzhm','经营者身份证号',:old.bzhm,:new.bzhm,:new.xgr,:new.xgrq);
				elsif :old.dwlb = '03' then
					insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
					values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'bzhm','负责人身份证号',:old.bzhm,:new.bzhm,:new.xgr,:new.xgrq);
				end if;
			end if;
			if :old.dbrzw <> :new.dbrzw and :old.dbrzw is not null  then
				if :old.dwlb = '01' then
					insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
					values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'dbrzw','法人职务',:old.dbrzw,:new.dbrzw,:new.xgr,:new.xgrq);
				elsif :old.dwlb = '03' then
					insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
					values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'dbrzw','负责人职务',:old.dbrzw,:new.dbrzw,:new.xgr,:new.xgrq);
				end if;
			end if;
			if :old.dblxdh <> :new.dblxdh and :old.dblxdh is not null  then
				if :old.dwlb = '01' then
					insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
					values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'dblxdh','法人联系电话',:old.dblxdh,:new.dblxdh,:new.xgr,:new.xgrq);
				elsif :old.dwlb = '02' then
					insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
					values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'dblxdh','经营者联系电话',:old.dblxdh,:new.dblxdh,:new.xgr,:new.xgrq);
				elsif :old.dwlb = '03' then
					insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
					values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'dblxdh','负责人联系电话',:old.dblxdh,:new.dblxdh,:new.xgr,:new.xgrq);
				end if;
			end if;
			if :old.lzlxr <> :new.lzlxr and :old.lzlxr is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'lzlxr','劳资办联系人',:old.lzlxr,:new.lzlxr,:new.xgr,:new.xgrq);
			end if;
			if :old.lzdh <> :new.lzdh and :old.lzdh is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'lzdh','劳资办电话',:old.lzdh,:new.lzdh,:new.xgr,:new.xgrq);
			end if;
			if :old.clrq <> :new.clrq and :old.clrq is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'clrq','成立日期',dp_sys.to_chn_date(:old.clrq,'ymd'),dp_sys.to_chn_date(:new.clrq,'ymd'),:new.xgr,:new.xgrq);
			end if;
			if :old.yyqrq <> :new.yyqrq and :old.yyqrq is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'yyqrq','营业起日期',dp_sys.to_chn_date(:old.yyqrq,'ymd'),dp_sys.to_chn_date(:new.yyqrq,'ymd'),:new.xgr,:new.xgrq);
			end if;
			if :old.yyzrq <> :new.yyzrq and :old.yyzrq is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'yyzrq','营业止日期',dp_sys.to_chn_date(:old.yyzrq,'ymd'),dp_sys.to_chn_date(:new.yyzrq,'ymd'),:new.xgr,:new.xgrq);
			end if;
			if :old.zczj <> :new.zczj and :old.zczj is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'zczj','注册资金',:old.zczj,:new.zczj,:new.xgr,:new.xgrq);
			end if;
			if :old.zcdz <> :new.zcdz and :old.zcdz is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'zcdz','注册地址',:old.zcdz,:new.zcdz,:new.xgr,:new.xgrq);
			end if;
			if :old.zcqx <> :new.zcqx and :old.zcqx is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'zcqx','注册区县',dp_sys.f_get_mc_by_dm('0001',:old.zcqx),dp_sys.f_get_mc_by_dm('0001',:new.zcqx),:new.xgr,:new.xgrq);
			end if;
			if :old.zcjd <> :new.zcjd and :old.zcjd is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'zcjd','注册地所在街道',dp_sys.f_get_mc_by_dm('0002',:old.zcjd),dp_sys.f_get_mc_by_dm('0002',:new.zcjd),:new.xgr,:new.xgrq);
			end if;
			if :old.jydz <> :new.jydz and :old.jydz is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'jydz','经营地址',:old.jydz,:new.jydz,:new.xgr,:new.xgrq);
			end if;
			if :old.jyjd <> :new.jyjd and :old.jyjd is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'jyjd','经营地所在街道',dp_sys.f_get_mc_by_dm('0002',:old.jyjd),dp_sys.f_get_mc_by_dm('0002',:new.jyjd),:new.xgr,:new.xgrq);
			end if;
			if :old.jyyzbm <> :new.jyyzbm and :old.jyyzbm is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'jyyzbm','经营地邮编',:old.jyyzbm,:new.jyyzbm,:new.xgr,:new.xgrq);
			end if;
			if :old.jyfw <> :new.jyfw and :old.jyfw is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'jyfw','经营范围',:old.jyfw,:new.jyfw,:new.xgr,:new.xgrq);
			end if;
			if :old.sshy <> :new.sshy and :old.sshy is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'sshy','所属行业',dp_sys.f_get_mc_by_dm('1004',:old.sshy),dp_sys.f_get_mc_by_dm('1004',:new.sshy),:new.xgr,:new.xgrq);
			end if;
			if :old.dwlx <> :new.dwlx and :old.dwlx is not null then
				insert into dw_xxbg(BGID,DWID,DWMC,DWLB,BGXM,BGXMMC,BGQNR,BGHNR,BGR,BGRQ)
				values(seq_bgid.nextval,:new.dwid,:old.dwmc,:new.dwlb,'dwlx','单位类型',dp_sys.f_get_mc_by_dm('1005',:old.dwlx),dp_sys.f_get_mc_by_dm('1005',:new.dwlx),:new.xgr,:new.xgrq);
			end if;
				
END;
/