日期:2014-05-18  浏览次数:20490 次

sql2000触发器批量删除时另一表的数据删不掉?
A表删除单条记录时,B表对应的记录自动删除了,但是批量删除A表时,B表的数据没自动删除?请教如何处理?

------解决方案--------------------
SQL code
基本上可以这样:

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