触发器中使用游标

最新推荐文章于 2024-02-03 17:19:54 发布

原创最新推荐文章于 2024-02-03 17:19:54 发布 · 2.1k 阅读

3 ·

CC 4.0 BY-SA版权

触发器专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一个SQL触发器的实现方案，该触发器用于在从表EMTinCar中删除记录后，自动调整剩余记录的排序顺序。通过使用游标遍历并更新受影响的记录，确保了车辆状态类型相同的记录在删除操作后仍能保持正确的顺序。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--删除后自己所在的重新排序
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