SQL Server中的嵌套事务 [英] Nested Transaction in SQL Server

问题描述

Hi Experts
我已存储过程并在SP之间的那个SP上应用事务.SP中有Trigger fire，并且Trigger也有事务应用，如果Trigger中发生任何异常或错误，则SP回滚"或提交"事务.

例如

Hi Experts
I Have Stored Procedure and apply transaction on that S.P in between of S.P There is Trigger fire in S.P and Trigger also have transaction apply and if there is any exception or error occure in Trigger then S.P "roll back" or "Commit" the transaction.

E.g

Create S.P abc
as
Begin Transaction
Try
select * From xyz
Update rst set a='1' where r='z' --when update trigger fire on rst table update trigger
select * from rst
Commit
catch
Rollback
end
End Transaction

Create Trigger nc on rst
update
Begin Transaction
Try
-----------any thing logic here-
Commit
catch
Rollback 
end
end Transaction

-但是在发生我的触发器异常"时，则S.P中的下一条语句是运行"或整个S.P回滚"
-或另一种情况假设我的触发器成功运行而没有错误，并且SP中出现错误，然后发生了.

-- But in My Trigger Exception Occurs then next statement in S.P is Run or Whole S.P Rollback
--Or Another Case Suppose My Trigger Run Successfully without error and error occur in S.P then But Happen.

推荐答案

您正在滥用触发器和存储过程.

最好将触发器功能与您的存储过程一起使用，以便您可以控制正在发生的事情.

You are abusing the trigger and stored procedures.

It is better to put the trigger functionality with in your stored procedure so you can control what is happening.

这篇关于SQL Server中的嵌套事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！