如我有表
A
ID ClassID State
1 1 1
2 1 0
3 2 1B
ClassID Valid
1 1
2 0--------------------------
要求是当
B记录中的Valid=1时更新A表中相应ClassID的State值为0
关将B表的Valid更新为0谢谢
A
ID ClassID State
1 1 1
2 1 0
3 2 1B
ClassID Valid
1 1
2 0--------------------------
要求是当
B记录中的Valid=1时更新A表中相应ClassID的State值为0
关将B表的Valid更新为0谢谢
Create Trigger TR_Update_B On A
For Update
As
Update B Set Valid = 0 From Inserted A Inner Join B On A.ClassID = B.ClassID
GO
Create Table A
(ID Int,
ClassID Int,
State Bit)
Insert A Select 1, 1, 1
Union All Select 2, 1, 0
Union All Select 3, 2, 1Create Table B
(ClassID Int,
Valid Bit)
Insert B Select 1, 1
Union All Select 2, 0
GO
--建立觸發器
Create Trigger TR_Update_B On A
For Update
As
Update B Set Valid = 0 From Inserted A Inner Join B On A.ClassID = B.ClassID
GO
--測試
Update A Set State = 0 From A Inner Join B On A.ClassID = B.ClassID Where B.Valid = 1Select * From A
Select * From B
GO
--刪除測試環境
Drop Table A, B
--結果
/*
ID ClassID State
1 1 0
2 1 0
3 2 1ClassID Valid
1 0
2 0
*/
或者trigger
解决一: 采用触发器
解决二: 用存储过程,两条更新sql语句(加事务)
解决三: 用SqlCommand对象,执行两条更新sql语句,注意需要加事务.