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


See full detail: http://www.dotnetheaven.com/Blogs/BlogDetail.aspx?BlogId=1220

Comments

Post a Comment

Popular posts from this blog

Very fast test data generation using exponential INSERT

Image
Introduction This article (my first) will describe an algorithm that enables a large amount of data to be generated very quickly using a SQL query. The test data can be static or incremental, such as “Item Name” and “Item ID”, respectively, as shown below: Background One of the tasks I did in a project involves generating a testing table with 103,680,000 records. The conventional method of data generation would take a month; hence, a fast method of data insertion was required. The new method took only 5 hours. Using the code Conventional method – Sequential INSERT The conventional way of generating a list of numbers from 0…100000 would be using a loop and an INSERT statement as follows: CREATE TABLE #tempTable([Item ID] [ bigint ], [Item Name] nvarchar ( 30 )) DECLARE @counter int SET @counter = 1 WHILE (@counter 100000 ) BEGIN INSERT INTO #tempTable VALUES (@counter, ' Hammer' ) SET @counter = @counter + 1 END SELECT * FROM #tem...
Read more

Basic concept and fundamentals of ASP.NET MVC (Model View Controller) Architecture

Image
This is basic concepts and fundamentals of ASP.NET Model View Controller (MVC) architecture workflow. MVC is stands for MODEL VIEW CONTROLLER. ASP.NET MVC is an architecture to develop ASP.NET web applications in a different manner than the traditional ASP.NET web development. Web applications developed with ASP.NET MVC are even more SEO (Search Engine) friendly. Microsoft .Net Framework 3.5 is minimum requirement to develop ASP.net MVC application.   MVC (Model View Controller) Interaction with Browser Like a normal web server interaction, MVC application also accepts requests and responds to the web browser in the same way.   Inside MVC Architecture The entire ASP.NET MVC architecture is based on Microsoft .NET Framework 3.5 and in addition uses LINQ to SQL Server. What is a Model? MVC   model   is basically a C# or VB.NET class A   model   is accessible by both   controller   and   view A   model   can be used to pass data ...
Read more

MVC Architecture Model In ASP.NET

Image
When developing ASP.NET applications the need of reducing code duplication increases along with their complexity. This is because testing and performing changes become very difficult tasks when having many different sources of code with the same functionality. Model View Controller architecture (or pattern) allows us to separate different parts of our applications into tiers to fulfill this need. MVC Overview Model View Controller architecture aims to separate an application into three parts: Model:  It is the business logic of an application. From an object oriented perspective it would consist of a set of classes that implement the critical functionality of an application from a business point of view. View:  It can consist of every type of interface given to the user. In ASP.NET the view is the set of web pages presented by a web application. Controller:  This part of the architecture is the most difficult to explain, hence the most difficult to implement in ...
Read more