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.
Best Answer
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:
The standard allocation stored procedure to robustly allocate a key or range of keys from a sequence is:
Then, given a simplified version of the table in the question:
We can assign sequence numbers per year using the following trigger:
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