0

I have scenario in which I got multiple records inserted into the Table even the checks are implemented, I explain the flow of the code below.

BEGIN Transection;
var model = select id from table1 where outlet_code = 123 and survey_id = 5
if(model){
     throw new Exception("Outlet is already Surveyed.");
}else{
     INSERT Record into the table
} 
Commit Transection;

Problem: I am getting multiple requests from the mobile side to upload survey, but the problem occur when I got multiple request with same outlet code.

First  Request outlet_code = 123abc and survey_id = 5
Second Request outlet_code = 123abc and survey_id = 5

Point is that Second request should not be inserted in the code however in some situations both records get inserted into the "table1"

I tested this scenario, but I got the exception which is fine, but there are records in the Database which violates this condition and I got both records in the database

kindly suggest what I am doing wrong,I just have an idea that it might be the problem of transaction committing order but I don't know how to solve this situation.

Database is SQL server 2008.

Eduard Uta
  • 2,477
  • 5
  • 26
  • 36
muneeb
  • 83
  • 9

1 Answers1

0

The easiest and cleanest way would be creating a primary key on ID columnn so table won't accept any inserts with duplicating IDs. This will throw an error though on a second insert so it should be handled as well.

Jemo
  • 117
  • 1
  • 7
  • actually there are three columns id -- pk outlet_code --- varchar survey_id ------ varchar id is auto increment, the only thing i dont want is another record with same surevey_id and same outlet_code – muneeb Jan 22 '16 at 09:54
  • then create an unique constraint on those columns: ALTER TABLE table_name ADD CONSTRAINT constr_name UNIQUE(outlet_code, surevey_id); --not sure if this is excactly the same in sql-server but you get the idea – Jemo Jan 22 '16 at 10:32