用户自己排序记录

 某一些情况，我们开发时，不能以添加先后进行排序，也不能以某一字段进行升序或降序排序。只好让用户自己排序。此篇就是以此要求，进行演示的。

数据表中，应有此排序的字段，可以根据记录的多少来定义它的数据类型，如INT等。数据的存储过程，应该应用到事务。以怕异常出现，而出现断序现象。

在添加时，应该找到存在记录中，最大的序号，如第一次添加，即是说最大记录为空，那它的的值为1。

在更新时，需要处理用户输入小于或等于 0值，它真正的值是1；而输入大于 最大排序号时，它应该于最大值。

 在删除时，应该是比被删除记录的排序号大的，都应该减1。

Insus.NET实现的效果如下：

 创建数据表

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Insus.NET
-- Create date: 2013-01-10
-- Description:    创建目录表
-- ============================================
CREATE TABLE [dbo].[Catalog](
    [Catalog_nbr] [smallint] IDENTITY(1,1) NOT NULL,
    [CatalogName] [nvarchar](100) NOT NULL,
    [IsActive] [bit] NOT NULL,
    [Sort] [int] NOT NULL,
    [CreateBy] [nvarchar](30) NOT NULL,
    [CreateDate] [datetime] NOT NULL,
    [UpdateBy] [nvarchar](30) NOT NULL,
    [UpdateDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [Catalog_nbr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
    [CatalogName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Catalog] ADD  DEFAULT ((1)) FOR [IsActive]
GO

ALTER TABLE [dbo].[Catalog] ADD  DEFAULT (getdate()) FOR [CreateDate]
GO

ALTER TABLE [dbo].[Catalog] ADD  DEFAULT (getdate()) FOR [UpdateDate]
GO

 

添加的存储过程：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:        Insus.NET
-- Create date: 2013-01-10
-- Description:    Add catalog.
-- =============================================
ALTER PROCEDURE [dbo].[usp_Catalog_Insert]
(
    @CatalogName NVARCHAR(100),
    @IsActive BIT,
    @CreateBy NVARCHAR(30)
)
AS
IF EXISTS(SELECT TOP(1) 1 FROM [dbo].[Catalog] WHERE [CatalogName] = @CatalogName)
BEGIN
    RAISERROR(N'目录已经存在，无法添加。',16,1)
    RETURN
END

DECLARE @UpdateBy NVARCHAR(30) = @CreateBy

--添加时找到最大的值

DECLARE @Sort INT
SELECT @Sort = MAX([Sort]) FROM [dbo].[Catalog]

INSERT INTO [dbo].[Catalog] ([CatalogName],[IsActive],[Sort],[CreateBy],[UpdateBy]) VALUES (@CatalogName,@IsActive,ISNULL(@Sort,0) + 1,@CreateBy,@UpdateBy)

更新的存储过程：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:        Insus.NET
-- Create date: 2013-01-10
-- Description:    update catalog.
-- =============================================
ALTER PROCEDURE [dbo].[usp_Catalog_Update]
(
    @Catalog_nbr SMALLINT,
    @CatalogName NVARCHAR(100),
    @IsActive BIT,
    @Sort INT,
    @UpdateBy NVARCHAR(30)
)
AS
IF EXISTS(SELECT TOP(1) 1 FROM [dbo].[Catalog] WHERE [CatalogName] = @CatalogName AND [Catalog_nbr] <> @Catalog_nbr)
BEGIN
    RAISERROR(N'目录已经存在，无法更新。',16,1)
    RETURN
END

DECLARE @mv INT
SELECT @mv = MAX([Sort]) FROM [dbo].[Catalog]

--如果更新的值比最大值大，那等于最大值。

IF @mv IS NOT NULL AND @Sort > @mv
    SET @Sort = @mv 

--如果更新的值为0，那等于1。
IF @Sort = 0
    SET @Sort = 1

UPDATE [dbo].[Catalog] SET [CatalogName] = @CatalogName, [IsActive] = @IsActive,[Sort] = @Sort,[UpdateBy] = @UpdateBy,[UpdateDate] = CURRENT_TIMESTAMP  WHERE [Catalog_nbr] = @Catalog_nbr

更新时的触发器：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:        Insus.NET
-- Create date: 2013-01-10
-- Description:    Catalog sort update.
-- =============================================
ALTER TRIGGER [dbo].[tri_Catalog_Update] ON [dbo].[Catalog]
FOR UPDATE
AS
DECLARE @PK INT,@OldValue INT, @NewValue INT,@MaxValue INT,@MinValue INT = 1
SELECT @PK = [Catalog_nbr],@OldValue = [Sort] FROM deleted
SELECT @NewValue = [Sort] FROM inserted 
SELECT @MaxValue = MAX([Sort]) FROM [dbo].[Catalog]


BEGIN 
     IF (@NewValue < @OldValue)
        UPDATE [dbo].[Catalog] SET [Sort] = [Sort] + 1 WHERE [Catalog_nbr] IN (SELECT [Catalog_nbr] FROM [dbo].[Catalog] WHERE [Sort] BETWEEN @NewValue AND (@OldValue - 1)) AND [Catalog_nbr] <> @PK
   
    IF (@NewValue > @OldValue)
        UPDATE [dbo].[Catalog] SET [Sort] = [Sort] - 1 WHERE [Catalog_nbr] IN (SELECT [Catalog_nbr] FROM [dbo].[Catalog] WHERE [Sort] BETWEEN (@OldValue + 1) AND @NewValue) AND [Catalog_nbr] <> @PK
END

删除记录时的触发器：

SET ANSI_NULLS ON
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
 -- ============================================= 
 -- Author: Insus.NET 
 -- Create date: 2013-01-10 
 -- Description: Catalog sort delete.
  -- ============================================= 
  ALTER TRIGGER [dbo].[tri_Catalog_Delete] ON [dbo].[Catalog] 
  FOR DELETE 
  AS 
  DECLARE @Value INT,@MaxValue INT 
  SELECT @Value = [Sort] FROM deleted 
  SELECT @MaxValue = MAX([Sort]) FROM [dbo].[Catalog] 
  BEGIN 
      IF (@Value < @MaxValue) 
      UPDATE [dbo].[Catalog] SET [Sort] = [Sort] - 1 WHERE [Catalog_nbr] IN (SELECT [Catalog_nbr] FROM [dbo].[Catalog] WHERE [Sort] BETWEEN @Value + 1 AND @MaxValue)
 END