触发器中使用游标

--删除后自己所在的重新排序
CREATE  trigger [dbo].[EMTinCar_deleted] 
on [dbo].[EMTinCar] 
after delete 
as

declare @ID int,@VehicleOrder_ID int,@VehicleStateType_ID int

select @ID=ID, @VehicleOrder_ID=VehicleOrder_ID,
 @VehicleStateType_ID=VehicleStateType_ID from   DELETED --获取删除之前的数据类型id


declare  OldorderDelete_cursor  cursor scroll for   

select ID ,VehicleOrder_ID  from EMTinCar where VehicleStateType_ID=@VehicleStateType_ID and VehicleOrder_ID>@VehicleOrder_ID

open OldorderDelete_cursor

fetch First from OldorderDelete_cursor into @ID ,@VehicleOrder_ID  

WHILE @@fetch_status=0   

BEGIN

Update EMTinCar Set VehicleOrder_ID=@VehicleOrder_ID-1 Where ID=@ID

if @VehicleOrder_ID<=0 --防止一直减到负数

set @VehicleOrder_ID=1

fetch next from OldorderDelete_cursor into @ID ,@VehicleOrder_ID  
   
end

CLOSE OldorderDelete_cursor  

DEALLOCATE OldorderDelete_cursor


GO