CREATE TRIGGER update_stock
ON dbo.tbl_test_order
FOR insert
AS

     declare @order_num int
     declare @orderno varchar(50)
     declare @product_no varchar(50)
     declare @color varchar(50)
     declare @product_size varchar(50)
     declare @int_flaw int


  begin TRANSACTION
           declare @nror int
           set @nror=0      
     
     /*通过订单编号获取到订单的数量，货号，色质码，尺码*/
         select @order_num=number,@orderno=orderno,@color=color,@product_size=product_size,@product_no=product_no from tbl_order where orderno=orderno   
         select @int_flaw=count(*) from tbl_order where orderno=@orderno and send like '%瑕疵%'

    if @int_flaw>0
       begin
            
      declare @stock_flaw int
      select @stock_flaw=number from tbl_stock where product_no=@product_no and color=@color and product_size=@product_size and stockid='3' and statusid='1'
      if @stock_flaw>= @order_num
         begin

            update tbl_stock set number=number-@order_num,number2=number2+@order_num  where product_no=@product_no and color=@color and product_size=@product_size and statusid='1' and stockid='3'
             set @nror=@nror+@@error
                  
             /*执行插入库存记录*/           
              insert into tbl_stock_inout_record (statusid,stockid,product_no,color,product_size) values ('1','1',@product_no,@color,@product_size)
             set @nror=@nror+@@error
             
              update tbl_order set stockid='3' , statusid='3' where orderno=@orderno
              set @nror=@nror+@@error   
         end    
     else
      begin     
          /*执行无货操作*/
           update tbl_order set statusid='13' where orderno=@orderno                       
       end                 
 end 

else 

begin
         
     /*查询库存表根据优先级来判断库存,并且状态是0的*/
     declare @stock_num int 
     select @stock_num=number from tbl_stock where product_no=@product_no and color=@color and product_size=@product_size and stockid='1' and statusid='1'
     
     /*如果库存表库存大于或等于订单数量*/
     if @stock_num>=@order_num
         begin
         /*执行扣减库存的操作*/
             update tbl_stock set number=number-@order_num,number2=number2+@order_num  where product_no=@product_no and color=@color and product_size=@product_size and statusid='1' and stockid='1'
             set @nror=@nror+@@error
                  
             /*执行插入库存记录*/           
              insert into tbl_stock_inout_record (statusid,stockid,product_no,color,product_size) values ('1','1',@product_no,@color,@product_size)
             set @nror=@nror+@@error
             
              update tbl_order set stockid='1' , statusid='3' where orderno=@orderno
              set @nror=@nror+@@error
             
         end 
     else 
        begin
         /*如果最优先的库存没有了，则查询第二优先级*/
          declare @stock_2_num int 
          
          select @stock_2_num=number from tbl_stock where  product_no=@product_no and color=@color and product_size=@product_size and stockid=2 and statusid=1
          
          /*此外再判断第二级库存是否大于订单数量时*/
           if @stock_2_num>=@order_num
             begin
                    update tbl_stock set number=number-@order_num where product_no=@product_no and color=@color and product_size=@product_size and statusid='1' and stockid='2'
                    set @nror=@nror+@@error
                             /*执行插入库存记录*/           

                    insert into tbl_stock_inout_record (statusid,stockid,product_no,color,product_size) values ('1','2',@product_no,@color,@product_size)
                    set @nror=@nror+@@error
             
                   update tbl_order set stockid='2' , statusid='3' where orderno=@orderno
                   set @nror=@nror+@@error
            
             end 
           else 
           /*如果不大于则执行第三等级*/
             begin
                     declare @stock_3_num int