求一个存储过程。现在有三个表:record,user,sys,结构如下: user record sys
{ userid { recordid {
userid PK, ------ recordid PK, -------- recordid PK,
money userid, flag,
} money, }
condition,
flag
}想做的操作步骤是:1
1。事务开始
2。给存储过程传入参数@condition和@userid.在record表中找出一些记录来(select * from record where condition=@condition and userid<>@userid)
3。对于1的结果中的每一个记录,按以下规则更新三个表
update record set flag=1 where recordid = 当前记录.recordid
update user set user.money = user.money+当前记录.money WHERE user.userid = 当前记录.userid
update sys set sys.flag =1 WHERE sys.recordid = 当前记录.recordid
4。事务结束
请大家帮帮我,解决即送分。谢谢大家!
{ userid { recordid {
userid PK, ------ recordid PK, -------- recordid PK,
money userid, flag,
} money, }
condition,
flag
}想做的操作步骤是:1
1。事务开始
2。给存储过程传入参数@condition和@userid.在record表中找出一些记录来(select * from record where condition=@condition and userid<>@userid)
3。对于1的结果中的每一个记录,按以下规则更新三个表
update record set flag=1 where recordid = 当前记录.recordid
update user set user.money = user.money+当前记录.money WHERE user.userid = 当前记录.userid
update sys set sys.flag =1 WHERE sys.recordid = 当前记录.recordid
4。事务结束
请大家帮帮我,解决即送分。谢谢大家!
Create procedure proName
@condition int,
@userid int
as
SET XACT_ABORT OFF
begin tran
Update record set flag=1 where condition=@condition and userid<>@userid
Update a set a.money = a.money+b.money
from [user] as a inner join record as b on a.userid = b.userid
and b.condition=@condition and b.userid<>@userid update sys set flag =1 WHERE recordid in (
Select recordid from record where condition=@condition and userid<>@userid)
commit
go
请问照您的写法,会不会发生一些数据不一致的问题呢?
比如执行过程中可不可以向record表插入满足@condition的数据?
As
Begin
Update record Set flag=1 where condition=@condition and userid<>@userid
Update A set money = money+B.money From [user] A Inner Join record B On A.userid=B.userid Where B.condition=@condition and B.userid<>@userid
update A set flag =1 From sys A Inner Join record B On A.recordid=B.recordid Where B.condition=@condition and B.userid<>@userid
End
GO
Create Proc UpdateTable(@condition Varchar(10),@userid Varchar(10))
As
BEGIN TRANSACTION
Update record Set flag=1 where condition=@condition and userid<>@userid
Update A set money = money+B.money From [user] A Inner Join record B On A.userid=B.userid Where B.condition=@condition and B.userid<>@userid
update A set flag =1 From sys A Inner Join record B On A.recordid=B.recordid Where B.condition=@condition and B.userid<>@userid
COMMIT
GO
begin tran
update record set flag=1 where condition=@condition and userid<>@userid;
update [user] set money = money+b.money from [user] a
inner join record b
on userid<>@userid and a.userid=b.userid and b.condition=@condition ;
update sys set flag =1 from sys a
inner join record b
on a.userid<>@userid and a.recordid=b.recordid and b.condition=@condition; commit
这样会不会发生一些数据不一致的问题呢?
比如执行过程中可不可以向record表插入满足@condition的数据?