SQL trigger to avoid real time registration errors

jeeva san

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?

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manikavelu Velayutham16y ago
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.
jeeva san16y ago
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?)
Manikavelu Velayutham16y ago
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.
jeeva san16y ago
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
Manikavelu Velayutham16y ago
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.