求助:一个MS-SQL触发器的写法(高手请进)

Question

各位:
   请帮我看看,如下一个MS-SQL触发器的写法是否正确,trigger是能运行,但在程序相应的增加时,就会死了,请会帮我看看是不是trigger的问题.谢谢./* 职务插入时，则职务提成表相应插入*/
drop trigger tr_position_insert
goCreate Trigger  tr_position_insert on aps_position for insert as
begin
    declare    @ls_earcode    varchar(3)
    declare    @ls_zptcode    varchar(8)
    declare    @ls_pstcode    varchar(8)
    declare    @ls_temp       varchar(8)
    declare    @ls_tmp_mty    varchar(8)
    declare    @ls_mtycode    varchar(10)
    declare @errmsg varchar(255)     if @@rowcount = 0 
   return     select @ls_pstcode = pst_code  from inserted     /*定义光标，产品记录*/
    declare cur_product cursor  for  
    select zpt_code from zsb_product 
    
    /*定义光标，会员卡提成记录*/
    declare cur_mbetype cursor for 
    select mty_code   from zsb_mbetype
    
       /*打开光标*/ 
       open cur_product
        FETCH Next from cur_product INTO @ls_zptcode
         while @@fetch_status = 0
         begin
  
           if @ls_zptcode is null select @ls_zptcode=''
   IF @ls_zptcode <> '' 
              begin
     /* message 'The product name is null for trigger tr_mbetype_insert'*/
              select @ls_temp=pdc_empposition 
                from zsb_pducommision
               where pdc_zptcode=@ls_zptcode
                 and pdc_empposition=@ls_pstcode
              
              if @ls_temp is null select @ls_temp=''
              if @ls_temp='' 
                begin
           insert into zsb_pducommision
         (ear_id,pdc_zptcode,pdc_empposition,pdc_empposname,pst_percentage,
                  pst_ifpct,pst_status,pst_percamt,pst_salcoms ,pst_salcamt,pst_ifsct,
                  pst_ascoms,pst_ascamt,pst_ifact,pst_ifsfns,pst_persubamt,pst_assubamt,pst_salsubamt)
         select '000',@ls_zptcode,pst_code,pst_name,0,
                  pst_ifpct,pst_status,0,0,0,'Y',
                  0,0,'Y',pst_ifsfns,0,0,0 from inserted             
                end           
                if @@error <> 0  goto error
            end
        end
        /*关闭光标*/
        close cur_product
        deallocate cur_product
 
       /*2打开光标*/
       open cur_mbetype
       FETCH Next from  cur_mbetype INTO @ls_mtycode
          while @@fetch_status = 0
          begin
           if @ls_mtycode is null select @ls_mtycode=''
   IF @ls_mtycode <> '' 
      /*message 'The mbe_type name is null for trigger tr_mbetype_insert' */
           
           begin
             select @ls_tmp_mty=mdc_empposition 
               from zsb_mbecommision
              where mdc_mtycode=@ls_mtycode
                and mdc_empposition=@ls_pstcode
              
              if @ls_tmp_mty is null select @ls_tmp_mty=''
              if @ls_tmp_mty=''
                 begin
                    insert into zsb_mbecommision
            (ear_id,mdc_mtycode,mdc_empposition,mdc_empposname,pst_ifmct,pst_mbecoms,
                    pst_mbecamt,pst_ifcontinue,pst_sencoms,pst_senamt,pst_status,pst_ifmfns)
            select '000',@ls_mtycode,pst_code,pst_name,pst_ifmct,0,                 
                    0,'D',0,0,pst_status,pst_ifmfns from inserted          
                 end                 if @@error <> 0  goto error
             end
        end
        /*关闭光标*/
        close cur_mbetype
        deallocate cur_mbetype
       
       /*插入存款提成表*/
       insert into zsb_stocommision(ear_id,mdc_code,mdc_empposition,mdc_empposname,
        pst_ifmct,pst_mbecoms,pst_mbecamt,pst_ifsct,pst_coupcoms,pst_coupcamt,pst_status)
select '000','007',pst_code,pst_name,
        'Y',0.00,0.00,'Y',0.00,0.00,'USE' from inserted
       
        if @@error <> 0  goto error           return
        /*  Errors handling  */
error: 
end
go其中:aps_position职务表;zsb_product为产品表,zsb_pducommision为职务对产品提成表,当职务增加时,对所有的产品应增加一个相应的提成记录.

Answer 1

游标循环里没有
FETCH Next from cur_product INTO @ls_zptcode
和FETCH Next from  cur_mbetype INTO @ls_mtycode
结果导致游标的@@fetch_status 始终是 0，导致死循环
解决方法，在循环体的最后部分加入
FETCH Next from cur_product INTO @ls_zptcode
和FETCH Next from  cur_mbetype INTO @ls_mtycode

Answer 2

修改后应该是这样的
drop trigger tr_position_insert
goCreate Trigger  tr_position_insert on aps_position for insert as
begin
    declare    @ls_earcode    varchar(3)
    declare    @ls_zptcode    varchar(8)
    declare    @ls_pstcode    varchar(8)
    declare    @ls_temp       varchar(8)
    declare    @ls_tmp_mty    varchar(8)
    declare    @ls_mtycode    varchar(10)
    declare @errmsg varchar(255)     if @@rowcount = 0 
       return     select @ls_pstcode = pst_code  from inserted     /*定义光标，产品记录*/
    declare cur_product cursor  for  
    select zpt_code from zsb_product 
    
    /*定义光标，会员卡提成记录*/
    declare cur_mbetype cursor for 
    select mty_code   from zsb_mbetype
    
       /*打开光标*/ 
       open cur_product
        FETCH Next from cur_product INTO @ls_zptcode
         while @@fetch_status = 0
         begin
  
           if @ls_zptcode is null select @ls_zptcode=''
       IF @ls_zptcode <> '' 
              begin
         /* message 'The product name is null for trigger tr_mbetype_insert'*/
              select @ls_temp=pdc_empposition 
                from zsb_pducommision
               where pdc_zptcode=@ls_zptcode
                 and pdc_empposition=@ls_pstcode
              
              if @ls_temp is null select @ls_temp=''
              if @ls_temp='' 
                begin
              insert into zsb_pducommision
             (ear_id,pdc_zptcode,pdc_empposition,pdc_empposname,pst_percentage,
                  pst_ifpct,pst_status,pst_percamt,pst_salcoms ,pst_salcamt,pst_ifsct,
                  pst_ascoms,pst_ascamt,pst_ifact,pst_ifsfns,pst_persubamt,pst_assubamt,pst_salsubamt)
             select '000',@ls_zptcode,pst_code,pst_name,0,
                  pst_ifpct,pst_status,0,0,0,'Y',
                  0,0,'Y',pst_ifsfns,0,0,0 from inserted             
                end           
                if @@error <> 0  goto error
            end
        FETCH Next from cur_product INTO @ls_zptcode-----------新添加
        end
        /*关闭光标*/
        close cur_product
        deallocate cur_product
 
       /*2打开光标*/
       open cur_mbetype
       FETCH Next from  cur_mbetype INTO @ls_mtycode
          while @@fetch_status = 0
          begin
           if @ls_mtycode is null select @ls_mtycode=''
           IF @ls_mtycode <> '' 
          /*message 'The mbe_type name is null for trigger tr_mbetype_insert' */
           
           begin
             select @ls_tmp_mty=mdc_empposition 
               from zsb_mbecommision
              where mdc_mtycode=@ls_mtycode
                and mdc_empposition=@ls_pstcode
              
              if @ls_tmp_mty is null select @ls_tmp_mty=''
              if @ls_tmp_mty=''
                 begin
                    insert into zsb_mbecommision
                (ear_id,mdc_mtycode,mdc_empposition,mdc_empposname,pst_ifmct,pst_mbecoms,
                    pst_mbecamt,pst_ifcontinue,pst_sencoms,pst_senamt,pst_status,pst_ifmfns)
                select '000',@ls_mtycode,pst_code,pst_name,pst_ifmct,0,                 
                    0,'D',0,0,pst_status,pst_ifmfns from inserted          
                 end                 if @@error <> 0  goto error
            end
            FETCH Next from  cur_mbetype INTO @ls_mtycode
        end
        /*关闭光标*/
        close cur_mbetype
        deallocate cur_mbetype
       
       /*插入存款提成表*/
       insert into zsb_stocommision(ear_id,mdc_code,mdc_empposition,mdc_empposname,
        pst_ifmct,pst_mbecoms,pst_mbecamt,pst_ifsct,pst_coupcoms,pst_coupcamt,pst_status)
    select '000','007',pst_code,pst_name,
        'Y',0.00,0.00,'Y',0.00,0.00,'USE' from inserted
       
        if @@error <> 0  goto error           return
        /*  Errors handling  */
    error: 
end
go