在MS SQL Server 2008中创建序列

Question

我编写了一个程序，可以在其中请求身份证。

身份证有不同类型（红色，蓝色，绿色）

在请求时，程序应生成标识号。数字（数字的范围）取决于请求的卡。

Red Card: 1 - 50000 
Blue Card: 50001 - 100000 
Green Card: 100001 - 150000

如果我将新的身份证添加到系统，则序列应自动为该新添加的身份证创建一个新的数字范围。数字不应该重复出现。一个号码只能使用一次。

我怎样才能做到这一点？有人可以帮我吗？

Answer 1

编辑＃1：我更新了触发器（IF UPDATE），存储过程和最后两个示例。

CREATE TABLE dbo.CustomSequence
(
    CustomSequenceID INT IDENTITY(1,1) PRIMARY KEY,
    SequenceName NVARCHAR(128) NOT NULL, -- or SYSNAME
        UNIQUE(SequenceName),
    RangeStart INT NOT NULL,
    RangeEnd INT NOT NULL,
        CHECK(RangeStart < RangeEnd),
    CurrentValue INT NULL,
        CHECK(RangeStart <= CurrentValue AND CurrentValue <= RangeEnd)
);
GO
CREATE TRIGGER trgIU_CustomSequence_VerifyRange
ON dbo.CustomSequence
AFTER INSERT, UPDATE
AS
BEGIN
     IF (UPDATE(RangeStart) OR UPDATE(RangeEnd)) AND EXISTS
    (
        SELECT  *
        FROM    inserted i 
        WHERE   EXISTS
        (
            SELECT  * FROM dbo.CustomSequence cs 
            WHERE   cs.CustomSequenceID <> i.CustomSequenceID
            AND     i.RangeStart <= cs.RangeEnd
            AND     i.RangeEnd >= cs.RangeStart
        )
    )
    BEGIN
        ROLLBACK TRANSACTION;
        RAISERROR(N'Range overlapping error', 16, 1);
    END
END;
GO
--TRUNCATE TABLE dbo.CustomSequence
INSERT  dbo.CustomSequence (SequenceName, RangeStart, RangeEnd)
SELECT  N'Red Card',        1,  50000 UNION ALL
SELECT  N'Blue Card',   50001, 100000 UNION ALL
SELECT  N'Green Card', 100001, 150000;
GO
-- Test for overlapping range
INSERT  dbo.CustomSequence (SequenceName, RangeStart, RangeEnd)
VALUES  (N'Yellow Card', -100, +100);
GO
/*
Msg 50000, Level 16, State 1, Procedure trgIU_CustomSequence_VerifyRange, Line 20
Range overlapping error
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
GO

-- This procedure tries to reserve 
CREATE PROCEDURE dbo.SequenceReservation
(
    @CustomSequenceID INT, -- You could use also @SequenceName 
    @IDsCount INT, -- How many IDs do we/you need ? (Needs to be greather than 0)
    @LastID INT OUTPUT
)   
AS
BEGIN
    DECLARE @StartTranCount INT, @SavePoint VARCHAR(32);
    SET @StartTranCount = @@TRANCOUNT;
    IF @StartTranCount = 0 -- There is an active transaction ?
    BEGIN
        BEGIN TRANSACTION -- If not then it starts a "new" transaction
    END
    ELSE -- If yes then "save" a save point -- see http://technet.microsoft.com/en-us/library/ms188378.aspx
    BEGIN
        DECLARE @ProcID INT, @NestLevel INT;
        SET @ProcID = @@PROCID;
        SET @NestLevel = @@NESTLEVEL;
        SET @SavePoint = CONVERT(VARCHAR(11), @ProcID) + ',' + CONVERT(VARCHAR(11), @NestLevel);
        SAVE TRANSACTION @SavePoint;
    END

    BEGIN TRY
        UPDATE  dbo.CustomSequence
        SET     @LastID = CurrentValue = ISNULL(CurrentValue, 0) + @IDsCount
        WHERE   CustomSequenceID = @CustomSequenceID;

        IF @@ROWCOUNT = 0
            RAISERROR(N'Invalid sequence', 16, 1);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @StartTranCount = 0
        BEGIN
            ROLLBACK TRANSACTION;
        END
        ELSE -- @StartTranCount > 0
        BEGIN
            ROLLBACK TRANSACTION @SavePoint
        END

        DECLARE @ErrorMessage NVARCHAR(2048), @ErrorSeverity INT, @ErrorState INT;
        SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState); 
    END CATCH;
END;
GO

SELECT * FROM dbo.CustomSequence;
GO

-- Example usage #1
DECLARE @LastID INT;
EXEC dbo.SequenceReservation  
        @CustomSequenceID = 1, -- Red Card
        @IDsCount = 2, -- How many IDs ?
        @LastID = @LastID OUTPUT;
SELECT @LastID - 2 + 1 AS [FirstID], @LastID AS [LastID];
GO

-- Example usage #2
DECLARE @LastID INT;
EXEC dbo.SequenceReservation  
        @CustomSequenceID = 1, -- Red Card
        @IDsCount = 7, -- How many IDs ?
        @LastID = @LastID OUTPUT;
SELECT @LastID - 7 + 1 AS [FirstID], @LastID AS [LastID];

SELECT * FROM dbo.CustomSequence;
GO

结果：

CustomSequenceID SequenceName RangeStart  RangeEnd    CurrentValue
---------------- ------------ ----------- ----------- ------------
1                Red Card     1           50000       9
2                Blue Card    50001       100000      NULL
3                Green Card   100001      150000      NULL