8

I have to add a trigger which should update a column using the following format strings: <current_date>_<per_day_incremental_id>, e.g 2015-10-01_36. Ids must be incremental and gaps are allowed.

My approach is rather naive: make a table with current date and current sequence value and maintain a single record in it:

create table DailySequence
(
    date date,
    sequence int
)

insert into DailySequence values (getdate(), 1);

CREATE TRIGGER MakeHumanReadableId ON dbo.AuditMeasures
FOR INSERT
AS
    DECLARE @ret int;
    DECLARE @tempDate date;
    DECLARE @nowDate date;

    SET @nowDate = getdate();

    SELECT @ret = t.sequence, @tempDate = t.date from DailySequence as t;

    IF @nowDate = @tempDate
    BEGIN
        SET @ret = @ret + 1;

        UPDATE DailySequence 
        SET sequence = @ret;
    END
    ELSE
    BEGIN
        SET @ret = 0;

        UPDATE DailySequence 
        SET sequence = @ret, date = @nowDate;
    END

    UPDATE AuditMeasures
    SET [HumanReadableId] = CAST(@nowdate AS VARCHAR(10)) + '_' + CAST(@ret AS VARCHAR(10));
    FROM inserted 
    INNER JOIN AuditMeasures On inserted.id = AuditMeasures.id
GO

Questions:

  • Are there any pitfalls for my solution? e.g code inside the trigger won't run inside a transaction thus giving incorrect values.
  • Am I missing a better solution?
3
  • Code inside the trigger certainly will run inside the context of the transaction that initiates the change in the underlying table.
    – Hannah Vernon
    Oct 1, 2015 at 17:47
  • 1
    Why do you need to store these values concatenated together? Do these values really need to be permanent rather than determined at runtime? Oct 1, 2015 at 18:42
  • 1
    If the gaps in IDs are allowed, then you can have a simple single IDENTITY that is not reset every day and append it to the current date. Each new day will look as if it has larger and larger "gap", but the gap is allowed, isn't it? It is a joke, of course, but it highlights that you must have omitted some requirements. Oct 2, 2015 at 3:02

2 Answers 2

4

One potential method of doing this would be (see the better method, at the end):

USE tempdb;

CREATE TABLE [dbo].[tblIDs]
(
    IDName nvarchar(255) NOT NULL
    , LastID int NULL,
    CONSTRAINT [PK_tblIDs] PRIMARY KEY CLUSTERED 
    (
        [IDName] ASC
    ) WITH 
    (
        PAD_INDEX = OFF
        , STATISTICS_NORECOMPUTE = OFF
        , IGNORE_DUP_KEY = OFF
        , ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON
        , FILLFACTOR = 100
    ) 
);
GO

CREATE PROCEDURE [dbo].[GetNextID](
    @IDName nvarchar(255)
)
AS
BEGIN
    /*
        Description:    Increments and returns the LastID value from 
                                tblIDs for a given IDName
        Author:         Max Vernon / Mike Defehr
        Date:           2012-07-19
    */

    DECLARE @Retry int;
    DECLARE @EN int, @ES int, @ET int;
    SET @Retry = 5;
    DECLARE @NewID int;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET NOCOUNT ON;
    WHILE @Retry > 0
    BEGIN
        BEGIN TRY
            UPDATE dbo.tblIDs 
            SET @NewID = LastID = LastID + 1 
            WHERE IDName = @IDName;

            IF @NewID IS NULL
            BEGIN
                SET @NewID = 1;
                INSERT INTO tblIDs (IDName, LastID) VALUES (@IDName, @NewID);
            END
            SET @Retry = -2; /* no need to retry since the operation completed */
        END TRY
        BEGIN CATCH
            IF (ERROR_NUMBER() = 1205) /* DEADLOCK */
                SET @Retry = @Retry - 1;
            ELSE
                BEGIN
                SET @Retry = -1;
                SET @EN = ERROR_NUMBER();
                SET @ES = ERROR_SEVERITY();
                SET @ET = ERROR_STATE()
                RAISERROR (@EN,@ES,@ET);
                END
        END CATCH
    END
    IF @Retry = 0 /* must have deadlock'd 5 times. */
    BEGIN
        SET @EN = 1205;
        SET @ES = 13;
        SET @ET = 1
        RAISERROR (@EN,@ES,@ET);
    END
    ELSE
        SELECT @NewID AS NewID;
END
GO

CREATE TABLE dbo.HumanReadableSequence
(
    HumanReadableSequence_ID VARCHAR(20) NOT NULL
        CONSTRAINT PK_HumanReadableSequence
        PRIMARY KEY CLUSTERED
    , SomeData VARCHAR(386) NOT NULL
);

GO
CREATE PROCEDURE dbo.HumanReadableSequence_Insert
(
    @SomeData VARCHAR(386)
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @NextID INT;
    DECLARE @Today VARCHAR(20);
    DECLARE @t TABLE 
    (
        ID INT NOT NULL
    );
    SET @Today = (CONVERT(VARCHAR(20), GETDATE(), 101))

    INSERT INTO @t (ID)
    EXEC dbo.GetNextID @IDName = @Today;

    INSERT INTO dbo.HumanReadableSequence (HumanReadableSequence_ID, SomeData)
    SELECT (@Today + '_' + CONVERT(VARCHAR(20), ID, 0))
        , @SomeData
    FROM @t;
END
GO

EXEC dbo.HumanReadableSequence_Insert N'this is a test';

SELECT *
FROM dbo.HumanReadableSequence;

The results:

enter image description here


Having said all that, I would ask why not simply maintain two separate columns that could be concatenated in the presentation layer:

CREATE TABLE dbo.HumanReadableSequence
(
    CreateDate DATETIME NOT NULL
        CONSTRAINT DF_HumanReadableSequence_CreateDate
        DEFAULT (DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())))
    , HumanReadableSequence_ID INT NOT NULL
    , SomeData VARCHAR(386) NOT NULL
    , CONSTRAINT PK_HumanReadableSequence
        PRIMARY KEY CLUSTERED
        (CreateDate, HumanReadableSequence_ID)
);

DECLARE @ID INT;
DECLARE @t TABLE 
(
    ID INT NOT NULL
);
DECLARE @Today VARCHAR(20);
SET @Today = (CONVERT(VARCHAR(20), GETDATE(), 101))

INSERT INTO @t (ID)
EXEC dbo.GetNextID @IDName = @Today;

SELECT @ID = t.ID
FROM @t t;

INSERT INTO dbo.HumanReadableSequence (SomeData, HumanReadableSequence_ID)
VALUES ('This is a test', @ID);

SELECT HumanReadableSequenceValue = 
        REPLACE(CONVERT(VARCHAR(20), hrs.CreateDate, 101) 
        + '_' 
        + CONVERT(VARCHAR(20), hrs.HumanReadableSequence_ID, 0), '/', '-')
    , SomeData
FROM dbo.HumanReadableSequence hrs;

The results:

enter image description here

The above method is much more capable of scaling well, and offers flexibility in the presentation of the human readable sequence number.

4

You could simplify the part updating the DailySequence table. Instead of this:

select @ret = t.sequence, @tempDate = t.date from DailySequence as t;
if @nowDate = @tempDate
begin
    set @ret = @ret + 1;
    update DailySequence set sequence = @ret;
end
else
begin
    set @ret = 0;
    update DailySequence set sequence = @ret, date = @nowDate;
end

you could use this:

UPDATE
  dbo.DailySequence
SET
  @ret = sequence = CASE date WHEN @nowDate THEN sequence + 1 ELSE 0 END,
  date = @nowDate
;

The @ret variable would thus be initialised in the UPDATE statement with the value stored into sequence.

Alternatively you could also get rid of the set @nowDate = getdate(); statement by rewriting the UPDATE like this:

UPDATE
  dbo.DailySequence
SET
  @ret     = sequence = CASE date WHEN CAST(GETDATE() AS date) THEN sequence + 1 ELSE 0 END,
  @nowDate = date     = GETDATE()
;

or, perhaps, even like this:

UPDATE
  dbo.DailySequence
SET
  @ret     = sequence = CASE date WHEN x.Today THEN sequence + 1 ELSE 0 END,
  @nowDate = date     = x.Today
FROM
  (SELECT CAST(GETDATE() AS date)) AS x (Today)
;

This way the UPDATE statement would be initialising both @nowDate and @ret. The @tempDate variable would not be needed with either option.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.