Preserve Primary Key Value - Always in sequence

Hello readers.
One of the major problem with primary key when set to auto increment is, when a row is deleted from the table the value of the primary key of that row is lost. So here is a small Trigger script for the table that will keep the primary key always in sequence that means this trigger always maintain the order or primary key like 1,2,3,4...

We need two triggers to make it possible. One check the value of primary key at time of Insert and other checks at Deletion.

First create a table 

CREATE TABLE AITEST (TBLID INT IDENTITY(1,1) PRIMARY KEY, TBLNAME NVARCHAR(50))

Trigger One

CREATE TRIGGER [dbo].[CHKSETIDENT] ON [dbo].[AITEST]

AFTER INSERT

AS

DECLARE @CURIDENT INT = (SELECT IDENT_CURRENT('AITEST'))

DECLARE @LASTROW INT = (SELECT COUNT(*) FROM AITEST)

IF @CURIDENT < @LASTROW

      BEGIN

            DBCC CHECKIDENT('AITEST', RESEED, @LASTROW)

            SELECT * FROM AITEST

      END

ELSE

      BEGIN

            SELECT * FROM AITEST

      END

GO

 Trigger Two

CREATE TRIGGER [dbo].[SETIDENT] ON [dbo].[AITEST]

AFTER DELETE

AS

DECLARE @DELROWS INT = (SELECT COUNT(*) FROM DELETED)

IF @DELROWS > 0

      BEGIN

            DECLARE @TOTROWS INT = (SELECT COUNT(*) FROM AITEST)

            IF @TOTROWS = 0

            BEGIN

                  DBCC CHECKIDENT('AITEST', RESEED, 0)

            END

      END

     

IF @DELROWS = 1

      BEGIN

            DECLARE @DELID INT = (SELECT TBLID FROM DELETED)

            DECLARE @CURIDENT INT = (@DELID-1)

            DBCC CHECKIDENT('AITEST', RESEED, @CURIDENT)

      END

GO

Copy and Paste the script, run and see the result.....