实现记录排序

以前曾经在asp.net实现记录排序功能，参考：http://www.cnblogs.com/insus/articles/1406440.html前端实现，写得很复杂。

现在，把它改为触发器来实现，那就简单多了。由于时隔已久，原实例样版代码已经找不到了，只有再写一样功能一样的表。这个表实现与记录插入功能，可以参考相关链接：

http://www.cnblogs.com/insus/articles/1921391.html

http://www.cnblogs.com/insus/articles/1921465.html

http://www.cnblogs.com/insus/articles/1921618.html

http://www.cnblogs.com/insus/articles/1922387.html

 

完整记录排序触发器代码(更新记录)，

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT [name] FROM sysobjects WHERE [name] = 'tri_OrderDemo_Update' AND TYPE = 'TR')
   DROP TRIGGER [dbo].[tri_OrderDemo_Update]
GO

CREATE TRIGGER [dbo].[tri_OrderDemo_Update] ON [dbo].[OrderDemo]
FOR UPDATE
AS
--宣告变量,存储更新记录的主键，[Num]字段的旧值，新值，以及所有记录的[Num]字段的最大值与最小值，最小值设为1
DECLARE @PK INT,@OldValue INT, @NewValue INT,@MaxValue INT,@MinValue INT = 1
--从deleted虚拟表找主键和[Num]字段到旧值
SELECT @PK = [OrderDemoId],@OldValue = [Num] FROM deleted
--从inserted虚拟表找[Num]字段到新值
SELECT @NewValue = [Num] FROM inserted 
--从表[OrdeDemo]找到最大值
SELECT @MaxValue = MAX([Num]) FROM [dbo].[OrderDemo]


BEGIN    
    --由大的排序号，排至小的排序号
     IF (@NewValue < @OldValue)
        UPDATE [dbo].[OrderDemo] SET [Num] = [Num] + 1 WHERE [OrderDemoId] IN (SELECT [OrderDemoId] FROM [dbo].[OrderDemo] WHERE [Num] BETWEEN @NewValue AND (@OldValue - 1)) AND [OrderDemoId] <> @PK
    --由小的排序号，排至大的排序号
    ELSE IF (@NewValue > @OldValue)
        UPDATE [dbo].[OrderDemo] SET [Num] = [Num] - 1 WHERE [OrderDemoId] IN (SELECT [OrderDemoId] FROM [dbo].[OrderDemo] WHERE [Num] BETWEEN (@OldValue + 1) AND @NewValue) AND [OrderDemoId] <> @PK
    ELSE
      --新值与旧值一样，不作更新@NewValue = @OldValue      
      RETURN
END

 

 删除记录触发器，

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT [name] FROM sysobjects WHERE [name] = 'tri_OrderDemo_Delete' AND TYPE = 'TR')
   DROP TRIGGER [dbo].[tri_OrderDemo_Delete]
GO


CREATE TRIGGER [dbo].[tri_OrderDemo_Delete] ON [dbo].[OrderDemo]
FOR DELETE
AS
--宣告变量,存储删除记录的[Num]字段的值
DECLARE @Value INT,@MaxValue INT
--从deleted虚拟表找主键和[Num]字段值
SELECT @Value = [Num] FROM deleted
SELECT @MaxValue = MAX([Num]) FROM [dbo].[OrderDemo]

BEGIN        
     IF (@Value < @MaxValue)
        UPDATE [dbo].[OrderDemo] SET [Num] = [Num] - 1 WHERE [OrderDemoId] IN (SELECT [OrderDemoId] FROM [dbo].[OrderDemo] WHERE [Num] BETWEEN @Value + 1 AND @MaxValue)
END