批量更新触发器

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[stock_update]
ON [dbo].[tbl_stock]
FOR UPDATE
AS

         begin TRANSACTION
           declare @nror int
           set @nror=0   
	
--if   (update(statusid)) 
--begin 
declare   @id   int 
declare @old_number int
declare @number int
declare @statusid int 
declare @old_statusid int 
declare @old_stock int


declare   new   cursor   local   for   select   [id],[number],[statusid]   from   inserted 
declare   old   cursor   local   for   select   [statusid] ,[number],[stockid]  from   deleted


open   new 
open   old

fetch   next   from   new   into   @id, @number,@statusid
fetch   next   from   old   into   @old_statusid, @old_number,@old_stock
while   @@fetch_status=0 
begin


--select   @id = id,@number=number,@statusid=statusid  from  inserted 

--select @old_statusid=statusid,@old_number=number,@old_stock=stockid from Deleted



declare @product_no varchar(50)
declare @color varchar(50)
declare @product_size varchar(100)
declare @stockid int 



select @product_no=product_no,@color=color,@product_size=product_size ,@stockid=stockid from tbl_stock where id=@id

			if @old_statusid=2 and @statusid=1
			--此处是在途库存确认收货步骤
			begin
			  			  
			  declare @count_num int
			  select @count_num=count(*) from tbl_stock where product_no=@product_no and color=@color and product_size=@product_size and statusid=1 and stockid=@stockid
			  if @count_num>1
			   begin
			    update tbl_stock set number=number+@number where product_no=@product_no and  product_size=@product_size and color=@color and statusid=1 and stockid=@stockid
			     set @nror=@nror+@@error
			     
			    delete tbl_stock where id=@id
			   		    
			    set @nror=@nror+@@error
			    insert into tbl_stock_inout_record(statusid,stockid,product_no,color,product_size,number,operation,operation_type,users) values ('1' ,@stockid,@product_no,@color,@product_size,@number,'在途确认','入库','')				    set @nror=@nror+@@error
			    
			     
			   end 
			   else 
			    begin
			       update tbl_stock set statusid='1'  where id=@id
			      set @nror=@nror+@@error
			       
			       insert into tbl_stock_inout_record(statusid,stockid,product_no,color,product_size,number,operation,operation_type,users) values ('1' ,@stockid,@product_no,@color,@product_size,@number,'在途确认','入库','')				    set @nror=@nror+@@error
			       
			     
			    end 			 			  			 
			end 
			
			else if @old_statusid=3 and @statusid=1
			--此处是采购确认收货
			 begin
			    declare @count_num_cai int
			    select @count_num_cai=count(*) from tbl_stock where product_no=@product_no and color=@color and product_size=@product_size and statusid=1 and stockid='2'
			    if @count_num_cai>0
			     begin
			       update tbl_stock set number=number+@number where product_no=@product_no and color=@color and product_size=@product_size and statusid=1 and stockid='2'
			    set @nror=@nror+@@error
			       
			       delete tbl_stock where id=@id
			      set @nror=@nror+@@error
			       
			       insert into tbl_stock_inout_record(statusid,stockid,product_no,color,product_size,number,operation,operation_type,users) values ('1' ,'2',@product_no,@color,@product_size,@number,'采购确认','入库','')				    set @nror=@nror+@@error
			      
			       
			     end 
			     
			     else 
			       begin
			       
			         update tbl_stock set statusid='1',stockid='2' where id=@id
			        set @nror=@nror+@@error
			         
			         insert into tbl_stock_inout_record(statusid,stockid,product_no,color,product_size,number,operation,operation_type,users) values ('1' ,'2',@product_no,@color,@product_size,@number,'采购确认','入库','')				    
			        set @nror=@nror+@@error
			       
			       end 
			    
			    			  
			 end 
			 
			 fetch   next   from   new   into   @id, @number,@statusid
             fetch   next   from   old   into   @old_statusid, @old_number,@old_stock
			 
	  end	
	  
	  
close   old 
close   new 
deallocate   new 
deallocate   old
	  
	  	 
--end 
 if @nror<>0
         
              begin
                rollback transaction
              end
           else
        
              begin
                commit transaction
             end 

CREATE TRIGGER [equpdate] ON [dbo].[equipment] 
FOR UPDATE 
AS 
declare @oldbh char(10),@newbh char(10) 

declare new cursor local for select num from inserted 
declare old cursor local for select num from deleted 

open new 
open old 

fetch next from new into @newbh 
fetch next from old into @oldbh 
while @@fetch_status=0 
begin 
if @oldbh <> @newbh update server_t set num=@newbh where num=@oldbh 
fetch next from new into @newbh 
fetch next from old into @oldbh 
end 

close old 
close new 
deallocate new 
deallocate old 
go

例子