一个存储过程,里面有4个操作,分别操作4张表。
其中一个是insert 表table1,但是违反了FOREIGN KEY 约束。但是另外三个操作却是成功的。
我是直接在sql server management studio里面写sql语句调用这个存储过程的。
exec procedure1 ...;照道理说,存储过程是有事务性的,要么所有的操作都成功,要么所有的操作都不成功。但是上面的情况是什么原因呢?谢谢!
其中一个是insert 表table1,但是违反了FOREIGN KEY 约束。但是另外三个操作却是成功的。
我是直接在sql server management studio里面写sql语句调用这个存储过程的。
exec procedure1 ...;照道理说,存储过程是有事务性的,要么所有的操作都成功,要么所有的操作都不成功。但是上面的情况是什么原因呢?谢谢!
--1.发表帖子回复
if OBJECT_ID('forum_PostTopicReply','p') is not null
drop procedure forum_PostTopicReply;
go
create procedure forum_PostTopicReply
@TopicID int,
@PosterID int,
@Contents nvarchar(1000),
@IP varchar(20),
@ImgUrl nvarchar(400)
as
--插入数据到表forum_topicReply
insert into forum_topicReply(TopicID,PosterID,Contents,IP,ImgUrl)
values(@TopicID,@PosterID,@Contents,@IP,@ImgUrl);
--更新表forum_topic
update forum_topic
set CountOfReply = CountOfReply + 1, LastUpdateTime = GETDATE()
where TopicID = @TopicID;
--更新表sys_user
update sys_user
set CountOfTopicReply = CountOfTopicReply + 1
where UserID = @PosterID;
--更新表forum_plate
update forum_plate
set CountOfTopicReply = CountOfTopicReply + 1
where PlateID = (select PlateID from forum_topic where TopicID = @TopicID);
go
begin ...Transaction
...
commit Transaction!
--1.发表帖子回复
if OBJECT_ID('forum_PostTopicReply','p') is not null
drop procedure forum_PostTopicReply;
go
create procedure forum_PostTopicReply
@TopicID int,
@PosterID int,
@Contents nvarchar(1000),
@IP varchar(20),
@ImgUrl nvarchar(400)
as
BEGIN TRANSACTION
--插入数据到表forum_topicReply
insert into forum_topicReply(TopicID,PosterID,Contents,IP,ImgUrl)
values(@TopicID,@PosterID,@Contents,@IP,@ImgUrl);
--更新表forum_topic
update forum_topic
set CountOfReply = CountOfReply + 1, LastUpdateTime = GETDATE()
where TopicID = @TopicID;
--更新表sys_user
update sys_user
set CountOfTopicReply = CountOfTopicReply + 1
where UserID = @PosterID;
--更新表forum_plate
update forum_plate
set CountOfTopicReply = CountOfTopicReply + 1
where PlateID = (select PlateID from forum_topic where TopicID = @TopicID);
COMMIT TRANSACTION
go
c#调用这个存储过程的时候用事务 commit和rollback都在外面用c#写 就可以解决这个问题了
insert into [存取记录表] ([账号],[存取类型], [存取金额]) values(@FromAccountNo, -1,@MoneyCount)
if @@error <> 0
begin
rollback transaction--发生错误则回滚事务,无条件退出l
return
end
insert into [存取记录表] ([账号],[存取类型], [存取金额]) values(@ToAccountNo, 1,@MoneyCount)
if @@error <> 0
begin
rollback tran
return
end
commit transaction --两条语句都完成,提交事务
加个@@error 这个试试。
{
bool result; SqlTransaction trans = null;
SqlConnection conn = null;
SqlCommand cmd = null; try
{
conn = new SqlConnection(Config.ConnectionString);
conn.Open();
trans = conn.BeginTransaction();
cmd = conn.CreateCommand();
cmd.Transaction = trans; cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "forum_PostTopicReply";
cmd.Parameters.Add(new SqlParameter("@TopicID", topicReply.TopicID));
cmd.Parameters.Add(new SqlParameter("@PosterID", topicReply.PosterID));
cmd.Parameters.Add(new SqlParameter("@Contents", topicReply.Contents));
cmd.Parameters.Add(new SqlParameter("@IP", topicReply.IP));
cmd.Parameters.Add(new SqlParameter("@ImgUrl", topicReply.ImgUrl));
cmd.ExecuteNonQuery(); trans.Commit(); result = true;
}
catch (Exception e)
{
result = false; if (trans != null)
{
trans.Rollback();
}
}
finally
{
if (trans != null)
{
trans.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
if (conn != null)
{
conn.Close();
conn.Dispose();
}
} return result;
}