Creating Custom ID on update SQL Server (trigger?)

Question

I have a table for requests and once they are approved/denied they need to be assigned a custom sequential ID like:

MYCODE-11-0001
MYCODE-11-0002
MYCODE-11-0003 

where MYCODE doesn't change but 11 is the current year and 0001 is sequentially generated number assigned for the year. So each year it will start at 0001.

I've never dealt with triggers or stored procedures in SQL Sever so I had the idea of doing this in code via Linq-to-SQL in VB.net and I have another table in SQL Server that keeps track of the Next ID

ID     NAME          NextID
1      Request2011    102
2      Request2012    1
3      Request2013    1

If they changed the status on the website the app selects the name based on the current year and grabs the NextID and generates the RequestID. LINQ will even catch if NextID has changed since grabbing the value, adding one, and trying to save that new value.

Pretty sure this will work but not I'm exactly satisfied with this because it assumes everything will come from my app. I'd feel much better about this if it was done on the database level and even better if it didn't rely on this NextID table I had.

Is there anything with triggers and store procedures that wouldn't be too crazy that would allow me to generate these custom IDs? I have a vague idea that i could run a trigger when the StatusID is updated to run a stored procedure to do some of the heavy duty work(generated RequestID if not already set), but if I still have to rely on the NextID table then I have to worry about locking/unlocking the table as well correct?

Just hoping from some advice from people who know SQL Server far better than I do.

Note: The Custom ID is only assigned at time of approving/denying the request not when it was inserted into the database. Each request does have it own unique auto-generated identity PK.

---

UPDATE: I may have to stick with the code solution as it does all the hand holding for me as far as concurrency goes, but i did write up a trigger just so i could learn.

/* --------------------
        SETUP 
--------------------*/
CREATE TABLE [dbo].[TestData](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [data] [varchar](100) NULL,
    [RequestID] [varchar](25) NULL,
    [StatusID] [int] NULL
    )

    INSERT INTO TestData
           ([data],[StatusID])
     VALUES
           ('test',1)
GO


CREATE TABLE [dbo].[NextID](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](75) NULL,
    [NextID] [int] NULL,
    [DateModified] [date] NULL,
    [ModifiedBy] [varchar](75) NULL
    )
    INSERT INTO NextID
           ([Name],[NextID])          
     VALUES('RequestID2011',1)           
GO

/* --------------------
        TRIGGER
--------------------*/
IF EXISTS (SELECT name FROM sys.objects
      WHERE name = 'UpdateStatusID' AND type = 'TR')
   DROP TRIGGER dbo.UpdateStatusID;
GO

CREATE TRIGGER UpdateStatusID
ON dbo.TestData
AFTER UPDATE 
AS 
IF ( UPDATE (StatusID))
BEGIN
    DECLARE @nextId int
    DECLARE @nextIdName varchar(50)

    /* -- Get NextID based on year's count IE: RequestID2011 */
    SET @nextIdName = 'RequestID' + CONVERT(VARCHAR, YEAR(GetDate()), 50)
    SET @nextid = (Select NextID from dbo.NextID where Name = @nextIdName)

    /* -- Increment NextID */
    UPDATE dbo.NextID set NextID = @nextid + 1 WHERE Name=@nextIdName

    /* -- Set New RequestID */
    UPDATE dbo.TestData
    SET RequestID = 'MYCODE-' + RIGHT(@nextIdName,2) + '-' + CONVERT(VARCHAR, REPLICATE('0', (4- LEN(@nextid))) + @nextid, 50)
    FROM inserted i INNER JOIN dbo.TestData t
    ON i.id = t.id 

END;
GO
/* --------------------
        TEST
--------------------*/
UPDATE dbo.TestData
SET StatusID = 3
WHERE ID = 1;
GO

What this doesn't do is protect against someone changing the StatusID more than once (they can do that but i only need to generate the ID once) or protect against people grabbing NextID at the same time and having a race condition problem.

This also still requires me to have several records of 'RequestIDXXXX' in the database for future years.

Warning: This doesn't handle concurrency and is just a work in process on my path to understanding triggers and SQL Server.

Answer 1

You can try something like this:

DECLARE @CustomID VARCHAR(255)
SELECT @CustomID = 'MYCODE-' + CAST((YEAR( GETDATE()) % 100) AS CHAR(2)) + '-' + REPLICATE('0', (4 - LEN((<Sequence> + 1)))) + CAST((<Sequence> + 1) AS VARCHAR(5))

EDIT

I don't know how you're looking to get the sequence, but if you're looking to pull it from a row count on the table, you can do something like this:

SELECT COUNT(SomethingID) --Optionally add +1
FROM   SomethingTable
WHERE  DATEDIFF(YEAR, SomeDateColumn, GETDATE()) = 0

Answer 2

You can indeed do it with a trigger on your table, see below:

create table NextID (
    id  int,
    name    varchar(10),
    nextid  int
)

create table TestData (
    sequence    varchar(14) NOT NULL,
    data    varchar(10)
)

insert into NextID
values(1, 'Req2011', 1)


--Important stuff starts here
ALTER TRIGGER MySequence
ON TestData
INSTEAD OF INSERT
AS
DECLARE @nextid int

SET @nextid = (select nextid from NextID ) - 1

UPDATE NextID
SET nextid = nextid + (select COUNT(*) from inserted)
WHERE id = 1;

WITH cte AS (
    select ROW_NUMBER() OVER (ORDER BY (select 1)) + @nextid as sequence, i.data
    from inserted i
)
insert into TestData
select 'MYCODE-' + RIGHT(YEAR(GETDATE()), 2) + '-' + RIGHT('000' + CAST(sequence as varchar), 4), data
from cte;

This is a pretty reliable way of doing it. Take note that the trigger is written to allow the insert of more than one record at a time.

Reference: http://msdn.microsoft.com/en-us/library/ms189799.aspx

http://www.sqlmag.com/article/sql-server/nondeterministic-row-numbers

Answer 3

I would suggest that this not be the "id", in the primary key line of thought.

You could use a persisted computed column instead. Something to the effect of:

ALTER TABLE myTable
ADD CustomID AS (

    'MYCODE-' + CAST(RIGHT(100 + year([date_field]),2) as varchar(2)) + CAST(RIGHT(10000 + [pk_field],4) as varchar(4))

) PERSISTED

This way, the work is done for you whenever a new record is added to the table.

The caution here is just that if you ever update the [pk_field] column (which is unlikely...?), your computed column will re-compute. But otherwise, as long as you use 'PERSISTED', it will remain un-changed.

EDIT
I replaced getdate() with [date_field] above. I still think a computed column is a good option for you, but the caveat is that you need a date field on the record (date_inserted, or what have you), as getdate() (or other non-deterministic values) cannot be used.

Answer 4

Yes, having such a table for the next ID is a hotspot and often a bottleneck, too - if done correctly. If not, then you'll get duplicate ID's....

Right now, SQL Server 2008 R2 doesn't really have a good answer for you. This might change with SQL Server 2011 (or possibly: SQL Server 2012), codenamed "Denali". Denali will introduce sequences, which allow you to create sequential ID's under the database core's control, and those are guaranteed to be unique. You can also reset sequences back to 1 on Jan 1 of each new year.

Check out Aaron Bertrand's blog post on SQL Server vNext (Denali): Using SEQUENCE for more information on sequences.

For now, yes, I guess your approach with either setting those ID's from your client-side using Linq-to-SQL, or inside a trigger, are probably the most suitable approaches (I would prefer the trigger solution, myself). I would think a single INSTEAD OF INSERT trigger on that table would suffice - if you insert a new row, get the new request ID you need and set it to the values you've just inserted.