Reset a sequence every year

Every time a new row is added to the table below, I want the sequence (Import Permit No) to be increased by 1....20160001, 20160002, etc and reset to 20170001, 20170002, etc in a new year.

CREATE TABLE [dbo].[tblPermits](
[ImportPermitID] [int] IDENTITY(1,1) NOT NULL,
[ImportPermitNo] [nchar](20) NULL,
[ImporterName] [int] NULL,
[Province] [varchar](50) NULL,
[LodgementDate] [datetime] NULL,
[PortofEntry] [int] NOT NULL,
[EstDateofArrival] [datetime] NULL,
[ConsignmentInvoicePONo] [varchar](50) NULL,
[OtherImportConditions] [varchar](400) NULL,
[Supplier] [int] NOT NULL,
[SupplierCountry] [varchar](50) NULL,
[CountryofOrigion] [int] NOT NULL,
CONSTRAINT [PK_tblPermits] PRIMARY KEY CLUSTERED 
(
[ImportPermitID] 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

Currently what I have is a trigger as shown below.

ALTER TRIGGER [dbo].[trgPermitsInsertImportPermitNo]
ON [dbo].[tblPermits] FOR INSERT
AS 
UPDATE dbo.tblPermits
SET ImportPermitNo = 'IP' + CAST(YEAR(i.LodgementDate) AS CHAR(4)) + RIGHT('000000' + CAST(i.ImportPermitID AS VARCHAR(6)), 6) 
FROM dbo.tblPermits p
INNER JOIN INSERTED i ON p.ImportPermitID = i.ImportPermitID

But I couldn't get the sequence in the trigger above to reset to 0001 in a new year.

How do I modify the trigger to reset the sequence in a new year?

• Rows can be deleted from this table. Other rows would not get renumbered in that case.

• The number of new rows in a year is expected to be less than 5000, but in any case the number of prefix zeros could be expanded in the final design to accommodate a larger range.

• Lodgement Date is a normal datetime field to be entered by the user.

• All entries are made immediately when received from the client. Dates from previous years turning up late will not be an issue.

• Duplicate ImportPermitNo values are not allowed.

In SQL Server 2012 or later, I would implement this using sequence objects.

For SQL Server 2008 R2, my replacement for that missing feature is Sequence Tables. In this case, there would be a key in the master sequence table for each year, for example:

CREATE TABLE dbo.SequenceTable
(
    sequence_name   nvarchar(20) NOT NULL,
    next_value      integer NOT NULL,

    CONSTRAINT [PK dbo.SequenceTable sequence_name]
    PRIMARY KEY CLUSTERED (sequence_name),
);
GO
INSERT dbo.SequenceTable
    (sequence_name, next_value)
VALUES
    (N'PermitIDs for 2016', 1),
    (N'PermitIDs for 2017', 1),
    (N'PermitIDs for 2018', 1);

The standard allocation stored procedure to robustly allocate a key or range of keys from a sequence is:

CREATE PROCEDURE dbo.Allocate_TSQL

    @SequenceName       nvarchar(20),   -- The name of the sequence to allocate keys from
    @RangeSize          integer = 1,    -- The number of keys to allocate
    @FirstAllocated     integer OUTPUT  -- The first key allocated (output)

AS
BEGIN

    SET XACT_ABORT ON;  -- Most errors will abort the batch
    SET NOCOUNT ON;     -- Supress 'x row(s) affected' messages
    SET ROWCOUNT 0;     -- Reset rowcount

    -- Validate the range size requested
    IF (@RangeSize IS NULL OR @RangeSize < 1)
    BEGIN
        RAISERROR('@RangeSize must be a positive integer (supplied value = %i)', 16, 1, @RangeSize);
        RETURN  999;
    END;

    -- Initialize the output parameter
    SET @FirstAllocated = NULL;

    -- Update the row associated with @SequenceName, returning the 
    -- current value, and then incrementing it by @RangeSize
    UPDATE dbo.SequenceTable WITH (READCOMMITTEDLOCK)
    SET @FirstAllocated = next_value,
        next_value = next_value + @RangeSize
    WHERE sequence_name = @SequenceName;

    -- If @Allocated has a non-NULL value, we know we successfully updated a row
    RETURN CASE WHEN (@FirstAllocated IS NOT NULL) THEN 0 ELSE -999 END; 
END;

Then, given a simplified version of the table in the question:

CREATE TABLE dbo.ImportPermits
(
    ImportPermitID integer IDENTITY (1, 1)
        CONSTRAINT [PK dbo.ImportPermits ImportPermitID]
        PRIMARY KEY CLUSTERED,
    ImportPermitNo nchar(12) NULL,
    LodgementDate datetime NULL
);

We can assign sequence numbers per year using the following trigger:

CREATE TRIGGER ImportPermitsImportPermitNo
ON dbo.ImportPermits
AFTER INSERT AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN;   -- Return immediately if no rows affected
    SET XACT_ABORT, NOCOUNT ON; -- Most errors abort the batch; no row count messages
    SET ROWCOUNT 0;             -- Ensure all rows are visible (local to trigger)

    DECLARE 
        @rc integer,
        @FirstAllocated integer,
        @RowCount integer,
        @Years integer,
        @SeqName nvarchar(20);

    -- Count rows and distinct lodgement years in the insert set
    SELECT 
        @RowCount = COUNT(*),
        @Years = COUNT(DISTINCT(YEAR(INS.LodgementDate))),
        @SeqName = N'PermitIDs for ' + 
            CONVERT(nchar(4), MIN(YEAR(INS.LodgementDate)))
    FROM Inserted AS INS;

    -- Check for multiple lodgement years (not implemented)
    IF @Years > 1
    BEGIN
        RAISERROR('Multiple LodgementDate years are not supported.', 16, 1);
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        RETURN;        
    END;

    -- Allocate the range of sequence numbers we will need
    EXECUTE @rc = dbo.Allocate_TSQL
        @SequenceName = @SeqName,
        @RangeSize = @RowCount,
        @FirstAllocated = @FirstAllocated OUTPUT;

    IF @rc <> 0
    BEGIN
        RAISERROR('Sequence allocation failed.', 16, 1);
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        RETURN;
    END;

    -- Assign ImportPermitNo values using the sequence numbers allocated
    WITH Sequenced AS
    (
        SELECT
            IP.ImportPermitNo,
            IP.LodgementDate,
            Seq = 
                @FirstAllocated - 1 + 
                ROW_NUMBER() OVER (
                    ORDER BY IP.LodgementDate ASC)
        FROM Inserted AS INS
        JOIN dbo.ImportPermits AS IP
            ON IP.ImportPermitID = INS.ImportPermitID
    )
    UPDATE Sequenced
    SET Sequenced.ImportPermitNo = 
        N'IP' + 
        CONVERT(nchar(4), YEAR(Sequenced.LodgementDate)) +
        RIGHT(N'000000' + CONVERT(nvarchar(11), Sequenced.Seq), 6);
END;

For brevity, the trigger is limited to inserting row(s) from a single year only, but it is not difficult to extend the logic.

Demo: db<>fiddle

StackExchange Database Administrators Q#159629, answer score: 6