sql-server - XACT_ABORT 的范围是什么

Question

SQL Server 2005 中 SET XACT_ABORT 语句的范围是什么？即:开始-结束 block 、过程或触发器、连接、数据库、服务器？

Answer 1

Technet Using Options in SQL Server暗示所有 SET 选项的范围都在连接或批处理级别。

MSDN SET Statements添加详细信息:

If a SET statement is run in a stored procedure or trigger, the value of the SET option is restored after control is returned from the stored procedure or trigger. Also, if a SET statement is specified in a dynamic SQL string that is run by using either sp_executesql or EXECUTE, the value of the SET option is restored after control is returned from the batch specified in the dynamic SQL string.

也可以通过 user options 默认为所有用户启用 XACT_ABORT :

EXEC sp_configure 'user options', 16384
RECONFIGURE WITH OVERRIDE

也可以仅通过自定义 logon trigger 对选定的用户强制执行.

另请参阅 XACT_ABORT behaviour 的重要详细信息.