日期:2014-05-18 浏览次数:20633 次
基本上可以这样:
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