I searched long and hard for this
solution on the net but to no avail but got hints on how to do it on a few
sites but the problem was I was that type of programmer who avoided using the
database for anything but storage. I preferred my database as a reservoir of
data no thinking/intelligence apart from the auto generated integers offered by
the DBMS until a client asked for a prefixed six digit auto generated
sequential code to identify each record in the system. This became my agony for
weeks, not that I was clueless but I had ideas (quick escape) a solution for
that moment as I think of a more permanent one. To cut the long story short all
those ideas failed but the one which worked is this one (using a trigger). I
know many of you will say y triggers not just a function to check the last
number(CHKLST) in the database and then just add 1 to that number, this is y,
in a distributed environment there is a chance that the function(CHKLST) which
checks the last value/number in the database gets indeed the value and increase
it by 1 and hands over to the function that writes (WRITER) to the database but
before the function(WRITER) writes to the database a delay occurs say network
congestion but the (CHKLST) manages to get the last value again before
function(WRITER) writes the new value. This means that a duplicate of values
will exist which is no good. I hope you’ve all gotten my point but the way I
resolved the problem is this any improvements are welcome; humans are learning
beings we can adapt to change.
ALTER TRIGGER [dbo].[triggerName]
ON [dbo].[tableName]
AFTER INSERT
AS
BEGIN
DECLARE
@DBautogeneratedvalue int;
DECLARE @code char(10);
SELECT
@DBautogeneratedvalue =autoid FROM inserted;
-- SET NOCOUNT ON added to prevent
extra result sets from
-- interfering with SELECT
statements.
if(@DBautogeneratedvalue
BETWEEN 1 AND 9)
SET @code ='X00000'+CONVERT(char(10),@DBautogeneratedvalue)
ELSE IF(@DBautogeneratedvalue
BETWEEN 10 AND
99)
SET @code ='X0000'+CONVERT(char(10),@DBautogeneratedvalue)
ELSE IF (@DBautogeneratedvalue
BETWEEN 100 AND
999)
SET @code ='X000'+CONVERT(char(10),@DBautogeneratedvalue)
ELSE IF (@DBautogeneratedvalue
BETWEEN 1000 AND
9999)
SET @code ='X00'+CONVERT(char(10),@DBautogeneratedvalue)
ELSE IF (@DBautogeneratedvalue
BETWEEN 10000 AND
99999)
SET @code ='X0'+CONVERT(char(10),@DBautogeneratedvalue)
ELSE
SET @code ='X'+CONVERT(char(10),@DBautogeneratedvalue)
SET NOCOUNT
ON;
UPDATE tableName SET Code=@code WHERE autoid=@DBautogeneratedvalue;
-- Insert
statements for trigger here
END
Where Code is the field of the required new code and autoid is the
field with the database auto generated integer number.