現在表:
合同主表:Contract(ContractID,CompanyID,ContractName.ContractVolume)
公司資訊表:Company(CompanyID,CompanyName,Re)
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 InsertedDeclare Company_DeleteCursor Cursor
For
Select Companyid From DeletedOpen Company_InsertCursor
Open Company_DeleteCursorFetch Next From Company_InsertCursor
Into @i_CompanyIDFetch Next From Company_DeleteCursor
Into @d_CompanyIDWhile @@Fetch_Status=0
BeginIF @i_CompanyID<>@d_Companyid
Begin
Update Contract
Set CompanyID=@i_CompanyID
Where CompanyID=@d_CompanyID
EndFetch Next From Company_InsertCursor
Into @i_CompanyIDFetch Next From Company_DeleteCursor
Into @d_CompanyIDEnd
Close Company_InsertCursor
Deallocate Company_InsertCursor
Close Company_DeleteCursor
Deallocate Company_DeleteCursor
End
但是更改公司資訊表中的公司號後,總是出現:"找不到要更新的資料列。最後讀取的值已被變更"這個問題,為什麽呀?要如何做才不會這樣?請高手指點,不勝感激,
合同主表:Contract(ContractID,CompanyID,ContractName.ContractVolume)
公司資訊表:Company(CompanyID,CompanyName,Re)
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 InsertedDeclare Company_DeleteCursor Cursor
For
Select Companyid From DeletedOpen Company_InsertCursor
Open Company_DeleteCursorFetch Next From Company_InsertCursor
Into @i_CompanyIDFetch Next From Company_DeleteCursor
Into @d_CompanyIDWhile @@Fetch_Status=0
BeginIF @i_CompanyID<>@d_Companyid
Begin
Update Contract
Set CompanyID=@i_CompanyID
Where CompanyID=@d_CompanyID
EndFetch Next From Company_InsertCursor
Into @i_CompanyIDFetch Next From Company_DeleteCursor
Into @d_CompanyIDEnd
Close Company_InsertCursor
Deallocate Company_InsertCursor
Close Company_DeleteCursor
Deallocate Company_DeleteCursor
End
但是更改公司資訊表中的公司號後,總是出現:"找不到要更新的資料列。最後讀取的值已被變更"這個問題,為什麽呀?要如何做才不會這樣?請高手指點,不勝感激,
合同主表:Contract(ContractID,CompanyID,ContractName.ContractVolume)
公司資訊表:Company(CompanyID,CompanyName,Re)
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'
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 InsertedDeclare Company_DeleteCursor Cursor
For
Select Companyid From DeletedOpen Company_InsertCursor
Open Company_DeleteCursorFetch Next From Company_InsertCursor
Into @i_CompanyIDFetch Next From Company_DeleteCursor
Into @d_CompanyIDWhile @@Fetch_Status=0
BeginIF @i_CompanyID<>@d_Companyid
Begin
Update Contract
Set CompanyID=@i_CompanyID
Where CompanyID=@d_CompanyID
EndFetch Next From Company_InsertCursor
Into @i_CompanyIDFetch Next From Company_DeleteCursor
Into @d_CompanyIDEnd
Close Company_InsertCursor
Deallocate Company_InsertCursor
Close Company_DeleteCursor
Deallocate Company_DeleteCursor
End
set nocount off