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 &lt; 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 &gt; 1
    BEGIN
        RAISERROR('Multiple LodgementDate years are not supported.', 16, 1);
        IF @@TRANCOUNT &gt; 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 &lt;&gt; 0
    BEGIN
        RAISERROR('Sequence allocation failed.', 16, 1);
        IF @@TRANCOUNT &gt; 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&lt;>fiddle