SQL trigger to avoid real time registration errors

Question

hi all i have create a form(web app) to register customer details with Submit button.Once i click button New the registration form will appear with new generated Account Number.after fill form click submit to save into SQL server database.. While one customer register his details another customer from other place Click button New to make registration. so same Account Number will generated and build problem when both click Submit button at same time. how could i prevent this problem ? if i use locking method to lock Account Number when click New. Is this possible?

Manikavelu Velayutham · Answer

Then you have to do some pessimistic kind of operation. Onclick of the submit button you need to check the table whether the newly generated number exists already if it exists just dont save it and prompt the user to submit it again so that a new number will be generated. In another way while checking in the table that the number already exists just store the next number while storing. Dont forget to mark this post as answered.

Manikavelu Velayutham · Answer

hope you are generating the account number in asp.net. Instead of generating there just modify your table as below. You can see the above code that UniqueId column is referenced to a user defined function instead of mentioning the datatype. This is called computed column. Just check the code in your query analyzer and then modify according toyour requirements.

Manikavelu Velayutham · Answer

Handle the new generated account number functionality in the table level. Surely there wont be any conflict in this approach. below code will help you in acheiving your goal CREATE FUNCTION udf_UniqueNumber( @UniqueID INT  @ID INT ) RETURNS VARCHAR ( 10 ) AS BEGIN DECLARE @Name VARCHAR ( 100 ) = ( SELECT [Name] FROM Names WHERE ID = @UniqueID ) DECLARE @RET VARCHAR ( 10 ) = CAST(@ID AS VARCHAR ( 10 )) DECLARE @LEN INT = LEN(@RET) SET @RET = UPPER( LEFT (@Name 3 )) + STUFF(@RET 1  0  LEFT ( ' 0000000'  7 - @LEN)) RETURN @RET END CREATE TABLE Names( ID INT Identity ( 1  1 ) NOT NULL PRIMARY KEY  NameID INT NOT NULL FOREIGN KEY REFERENCES Person(ID) UniqueId AS dbo.udf_SET_JobCode(PersonID ID) -- Unique Number Generation Comments VARCHAR ( 200 ) NOT NULL ) Just insert the values in the table Names. UniqueId will be automatically generated. You can remodify the user defined function as per your Unique Id generation Dont forget to mark this post as answered.

jeeva san · Answer

hi... there is any chance to solve this problem without modify the database? (if i create the same Account ID when click Submit button.Hw can make it?)

jeeva san · Answer

Thanks where to use this codes? can you explain more ( .Once i click button New the registration form will appear with new generated Account Number.after fill form click submit to save into SQL server database) when i click NEW button the data will generated in form only and not saved into database