修改数据触发器

CREATE  trigger [dbo].[EMTinCar_update] 
on [dbo].[EMTinCar] 
for update 
as
if update(SetTime)

BEGIN

declare @ID int,@VehicleOrder_ID int,@VehicleStateType_ID int,@EMCarStateType_ID int,@VehicleStateType_IDOld int,@VehicleOrderOld int;
select @ID=p2.ID,@EMCarStateType_ID=emcar1.EMCarStateType_ID FROM inserted p1
inner JOIN deleted p2 ON p1.id=p2.id
INNER JOIN EMCar emcar1 on p1.EMCar_ID=emcar1.ID 


IF (@EMCarStateType_ID!=1)
begin
set @VehicleStateType_ID=4 --类型不显示

end
else 
begin
set @VehicleStateType_ID=1 

end
SELECT @VehicleOrder_ID= max(VehicleOrder_ID)  FROM EMTinCar WHERE VehicleStateType_ID=@VehicleStateType_ID
IF(@VehicleOrder_ID is null)
begin
set @VehicleOrder_ID=0
end

set @VehicleOrder_ID=@VehicleOrder_ID+1

--修改之前把自己保存起来
select @VehicleStateType_IDOld= VehicleStateType_ID from deleted where ID=@ID

select @VehicleOrderOld=VehicleOrder_ID from deleted  where ID=@ID ;

update EMTinCar set VehicleStateType_ID=@VehicleStateType_ID,VehicleOrder_ID=@VehicleOrder_ID where ID=@ID



--自己的排序，获取更新之前所在的数据
declare  Oldorder_cursor  cursor scroll for   

select ID ,VehicleOrder_ID from EMTinCar where VehicleStateType_ID=@VehicleStateType_IDOld and VehicleOrder_ID>=@VehicleOrderOld order BY VehicleOrder_ID asc

open Oldorder_cursor

fetch First from Oldorder_cursor into @ID ,@VehicleOrder_ID  

WHILE @@fetch_status=0   

BEGIN

IF @VehicleStateType_IDOld!=@VehicleStateType_ID --


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


if @VehicleOrder_ID<=0

set @VehicleOrder_ID=1

fetch next from Oldorder_cursor into @ID ,@VehicleOrder_ID     

end

CLOSE Oldorder_cursor  

DEALLOCATE Oldorder_cursor
  
end


GO