日期:2014-05-18 浏览次数:20548 次
基本上可以这样: CREATE TRIGGER write_report01 ON [dbo].[zcjgmxb] FOR INSERT, UPDATE, DELETE AS begin declare @s_hsdw varchar(10), --原表的核算单位 @n_id numeric(9), --原表的ID @d_kjrq datetime, --原表的记账日期 @s_zffs varchar(2), --原表的支付方式 @s_jjyt varchar(2), --原表的经济用途 @m_je money , --原表的支付金额 @s_import_pc varchar(10) , --导入的批次,如果是0000表示手工录入的账 @m_zjzf money , --报表的直接直付 @m_sqzf money , --报表的授权支付 @m_zhzf money , --报表的专户支付 @m_jjyt01 money ,--报表的经济用途 @m_jjyt02 money ,--报表的经济用途 @m_jjyt03 money ,--报表的经济用途 @m_jjyt04 money ,--报表的经济用途 @m_jjyt05 money ,--报表的经济用途 @m_jjyt06 money --报表的经济用途 select @s_hsdw = chsdw01,@n_id = id,@d_kjrq = dzcrq, @s_zffs =szffs01,@s_jjyt=stzxt_jjyt01, @m_je = mje,@s_import_pc=import_pc From inserted --得到所有变量值 if @s_zffs = '01' set @m_zjzf=@m_je else if @s_zffs='02' set @m_sqzf=@m_je else if @s_zffs='03' set @m_zhzf=@m_je --分解经济用济 if @s_jjyt='01' set @m_jjyt01=@m_je else if @s_jjyt='02' set @m_jjyt02=@m_je else if @s_jjyt='03' set @m_jjyt03=@m_je else if @s_jjyt='04' set @m_jjyt04=@m_je else if @s_jjyt='05' set @m_jjyt05=@m_je else if @s_jjyt='06' set @m_jjyt06 = @m_je if exists(select 1 from inserted) and not exists(select 1 from deleted)--新增 begin if @s_import_pc='0000' begin insert into report_01(source_id,hsdw,kjrq,zjzf_je,sqzf_je,zhzf_je,jjyt01_je,jjyt02_je, jjyt03_je,jjyt04_je,jjyt05_je,jjyt06_je ) values (@n_id,@s_hsdw,@d_kjrq,@m_zjzf,@m_sqzf,@m_zhzf,@m_jjyt01,@m_jjyt02,@m_jjyt03, @m_jjyt04,@m_jjyt05,@m_jjyt06) end else begin insert into report_01(source_id,hsdw,kjrq,zjzf_je,sqzf_je,zhzf_je,jjyt01_je,jjyt02_je, jjyt03_je,jjyt04_je,jjyt05_je,jjyt06_je ) values (@n_id,@s_hsdw,@d_kjrq,@m_zjzf,@m_sqzf,0,@m_jjyt01,@m_jjyt02,@m_jjyt03,@m_jjyt04, @m_jjyt05,@m_jjyt06) end end if exists(select 1 from inserted) and exists(select 1 from deleted) --修改 begin update report_01 set zjzf_je=@m_zjzf,sqzf_je=@m_sqzf,zhzf_je=@m_zhzf,jjyt01_je=@m_jjyt01, jjyt02_je=@m_jjyt02,jjyt03_je=@m_jjyt03,jjyt04_je=@m_jjyt04,jjyt05_je=@m_jjyt05,jjyt06_je =@m_jjyt06 where source_id=cast(@n_id as varchar(10)) end if not exists(select 1 from inserted) --删除 begin --select @n_id = id from deleted --得到ID多余的上面已赋值 delete from report_01 where source_id=cast(@n_id as varchar(10)) end END