触发器疑难问题，真烦心
SQL codeCREATE TRIGGER update_stockON dbo.tbl_test_orderFOR insertAS 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 [email protected] and send like '%瑕疵%' if @int_flaw>0 begin declare @stock_flaw int select @stock_flaw=number from tbl_stock where [email protected]_no and [email protected] and [email protected]_size and stockid='3' and statusid='1' if @stock_flaw>= @order_num begin update tbl_stock set [email protected]_num,[email protected]_num where [email protected]_no and [email protected] and [email protected]_size and statusid='1' and stockid='3' set @[email protected]+@@error /*执行插入库存记录*/ insert into tbl_stock_inout_record (statusid,stockid,product_no,color,product_size) values ('1','1',@product_no,@color,@product_size) set @[email protected]+@@error update tbl_order set stockid='3' , statusid='3' where [email protected] set @[email protected]+@@error end else begin /*执行无货操作*/ update tbl_order set statusid='13' where [email protected] end end else begin /*查询库存表根据优先级来判断库存,并且状态是0的*/ declare @stock_num int select @stock_num=number from tbl_stock where [email protected]_no and [email protected] and [email protected]_size and stockid='1' and statusid='1' /*如果库存表库存大于或等于订单数量*/ if @stock_num>[email protected]_num begin /*执行扣减库存的操作*/ update tbl_stock set [email protected]_num,[email protected]_num where [email protected]_no and [email protected] and [email protected]_size and statusid='1' and stockid='1' set @[email protected]+@@error /*执行插入库存记录*/ insert into tbl_stock_inout_record (statusid,stockid,product_no,color,product_size) values ('1','1',@product_no,@color,@product_size) set @[email protected]+@@error update tbl_order set stockid='1' , statusid='3' where [email protected] set @[email protected]+@@error end else begin /*如果最优先的库存没有了，则查询第二优先级*/ declare @stock_2_num int select @stock_2_num=number from tbl_stock where [email protected]_no and [email protected] and [email protected]_size and stockid=2 and statusid=1 /*此外再判断第二级库存是否大于订单数量时*/ if @stock_2_num>[email protected]_num begin update tbl_stock set [email protected]_num where [email protected]_no and [email protected] and [email protected]_size and statusid='1' and stockid='2' set @[email protected]+@@error /*执行插入库存记录*/ insert into tbl_stock_inout_record (statusid,stockid,product_no,color,product_size) values ('1','2',@product_no,@color,@product_size) set @[email protected]+@@error update tbl_order set stockid='2' , statusid='3' where [email protected] set @[email protected]+@@error end else /*如果不大于则执行第三等级*/ begin declare @stock_3_num int /*执行全国货*/ select @stock_3_num=number from tbl_stock where [email protected]_no and [email protected] and [email protected]_size and stockid=4 and statusid=1 if @stock_3_num>[email protected]_num begin update tbl_stock set [email protected]_num where [email protected]_no and [email protected] and [email protected]_size and statusid='1' and stockid='4' set @[email protected]+@@error /*执行插入库存记录*/ insert into tbl_stock_inout_record (statusid,stockid,product_no,color,product_size) values ('1','4',@product_no,@color,@product_size) set @[email protected]+@@error update tbl_order set stockid='4' , statusid='13' where [email protected] set @[email protected]+@@error end else begin /*执行无货操作*/ update tbl_order set statusid='13' where [email protected] end end end end if @nror<>0 begin rollback transaction end else begin commit transaction end