如何防止sql存储过程中的重复更新

I have this table in Sql and in insert Form of my project I prevent the user from entering same HDate for Same HNumber

我在Sql中有这个表，在项目的插入形式中，我阻止用户为相同的HNumber输入相同的HDate

RecID       HDate        HNumber     HComb
----------------------------------------------
1           2017-1-30       1          12
3           2017-1-29       1          15
5           2017-1-30       2          12
6           2017-1-30       3          12
9           2017-1-30       4          12

But in Edit Form I don't know how to prevent that,

但是在编辑表单中，我不知道如何防止，

I try this code in stored procedure but it work for some HNumber, But it prevent some other HNumber to be edited in it's own date

我在存储过程中尝试了这个代码，但是它对某些HNumber有效，但是它阻止了其他一些HNumber在它自己的日期被编辑。

Create Procedure UpdCombHarByRecID

@RecID int,
@HarvestDate Date,
@HiveNumber int,
@HoneyComb Float, 

as

if NOT Exists (Select * From tHoneyHarvest Where RecID=@RecID)
return 0   
//there is no record to be updated

if Exists (Select * From tHoneyHarvest Where HarvestDate=@HarvestDate AND
 HiveNumber=HiveNumber And
 RecID!=@RecID) 
 // I hoped this should do the job 
 //(RecID is PrimaryKey and it is identity)
return 2

Update tHoneyHarvest 

Set HarvestDate=@HarvestDate,
HoneyType=@HoneyType,
HoneyComb=@HoneyComb,
HoneyDetails=@HoneyDetails

Where RecID=@RecID
return 1

now where is the problem?

问题出在哪里?

2 个解决方案

#1

The best way is use non Clustered index .non Clustered index prevent the duplicate records when Inserts or update is going to occur .

最好的方法是使用非聚集索引。非聚集索引防止在插入或更新将要发生时重复记录。

CREATE UNIQUE INDEX MyIndex ON ExcelTable(HDate, HNumber)

please refer to

请参考

https://supportline.microfocus.com/documentation/books/sx50/rhsqlx1s.htm

https://supportline.microfocus.com/documentation/books/sx50/rhsqlx1s.htm

Add unique constraint to combination of two columns

为两列的组合添加惟一约束

#2

tanks to the help in comment the problem was a miss type in procedure

坦克在注释中帮助注释的问题是在程序中漏掉的类型

if Exists (Select * From tHoneyHarvest Where HarvestDate=@HarvestDate AND
  HiveNumber=@HiveNumber And
  RecID!=@RecID)

I was forgot to add @ befor HiveNumber

我忘记加@了

#1