Introduction This article provides a simple example of using jQuery along with the jQuery tablesorter and tablesorter.pager plug-ins to provide sorti
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
Comments