当前位置: 代码迷 >> Sql Server >> 找不到要更新的資料列。最後讀取的值已被變更解决方法
  详细解决方案

找不到要更新的資料列。最後讀取的值已被變更解决方法

热度:91   发布时间:2016-04-27 16:58:14.0
找不到要更新的資料列。最後讀取的值已被變更
現在表:
合同主表:Contract(ContractID,CompanyID,ContractName.ContractVolume)
公司資訊表:Company(CompanyID,CompanyName,Remark)
ContractID,CompanyID分別為主鍵.
要完成的任務:
當更改公司號(Companyid)後,合同中對應公司的公司號也隨著變更
SQL代碼如下:
Create   Trigger     [Company_Update]     on     Company
For   Update
As
Begin
Declare   @i_CompanyID   Varchar(20),@d_CompanyID   Varchar(20)

Declare   Company_InsertCursor   Cursor
For
Select   CompanyID   From   Inserted

Declare   Company_DeleteCursor   Cursor
For
Select   Companyid   From   Deleted

Open   Company_InsertCursor
Open   Company_DeleteCursor

Fetch   Next   From   Company_InsertCursor  
Into   @i_CompanyID

Fetch   Next   From   Company_DeleteCursor
Into   @d_CompanyID

While   @@Fetch_Status=0
Begin

IF   @i_CompanyID <> @d_Companyid
Begin
Update   Contract
Set   [email protected]_CompanyID
Where   [email protected]_CompanyID
End

Fetch   Next   From   Company_InsertCursor  
Into   @i_CompanyID

Fetch   Next   From   Company_DeleteCursor
Into   @d_CompanyID

End
Close   Company_InsertCursor
Deallocate   Company_InsertCursor
Close   Company_DeleteCursor
Deallocate   Company_DeleteCursor
End
但是更改公司資訊表中的公司號後,總是出現:"找不到要更新的資料列。最後讀取的值已被變更"這個問題,為什麽呀?要如何做才不會這樣?請高手指點,不勝感激,


------解决方案--------------------
現在表:
合同主表:Contract(ContractID,CompanyID,ContractName.ContractVolume)
公司資訊表:Company(CompanyID,CompanyName,Remark)
ContractID,CompanyID分別為主鍵.
要完成的任務:
當更改公司號(Companyid)後,合同中對應公司的公司號也隨著變更

update A
set CompanyID=B.CompanyID
from Contract A
inner join Company B
on A.CompanyID=B.CompanyID
where B.CompanyID= 'c00001 '
------解决方案--------------------
不要使用触发器,用外键约束来实现对Contract表CompanyID的更新
------解决方案--------------------
代码改成:
Create Trigger [Company_Update] on Company
For Update
As
set nocount on
Begin
Declare @i_CompanyID Varchar(20),@d_CompanyID Varchar(20)

Declare Company_InsertCursor Cursor
For
Select CompanyID From Inserted

Declare Company_DeleteCursor Cursor
For
Select Companyid From Deleted

Open Company_InsertCursor
Open Company_DeleteCursor

Fetch Next From Company_InsertCursor
Into @i_CompanyID

Fetch Next From Company_DeleteCursor
Into @d_CompanyID

While @@Fetch_Status=0
Begin

IF @i_CompanyID <> @d_Companyid
Begin
Update Contract
Set [email protected]_CompanyID
Where [email protected]_CompanyID
End

Fetch Next From Company_InsertCursor
Into @i_CompanyID

Fetch Next From Company_DeleteCursor
Into @d_CompanyID

End
Close Company_InsertCursor
Deallocate Company_InsertCursor
Close Company_DeleteCursor
Deallocate Company_DeleteCursor
End
set nocount off
  相关解决方案