在触发器中执行的方法中发生异常时，日志不持久 [英] Logging not Persisting When Exception Occurs in Method Executed in a Trigger
问题描述
我整天都在这个问题上受困，似乎无法在线找到任何指向我的可能原因。
I have been stuck all day on this issue and cannot seem to find anything online pointing me to what might be causing it.
我在以下方法中记录器类，下面的代码调用记录器。当没有异常发生时，所有日志语句都会正常运行，但是，当发生异常时，日志语句将根本不会运行（但是它们确实是通过Web服务调用运行的。）。
I have the below logging method in a Logger class and the below code calling the logger. When no exception occurs all the log statements work perfectly, however when an exception occurs the log statements do not run at all (however they do run from the web service call).
记录器日志方法：
public static Guid WriteToSLXLog(string ascendId, string masterDataId, string masterDataType, int? status,
string request, string requestRecieved, Exception ex, bool isError)
{
var connection = ConfigurationManager.ConnectionStrings["AscendConnectionString"];
string connectionString = "context connection=true";
// define INSERT query with parameters
var query =
"INSERT INTO " + AscendTable.SmartLogixLogDataTableName +
" (LogID, LogDate, AscendId, MasterDataId, MasterDataType, Status, Details, Request, RequestRecieved, StackTrace, IsError) " +
"VALUES (@LogID, @LogDate, @AscendId, @MasterDataId, @MasterDataType, @Status, @Details, @Request, @RequestRecieved, @StackTrace, @IsError)";
var logId = Guid.NewGuid();
using (var cn = new SqlConnection(connectionString))
{
if (!cn.State.Equals(ConnectionState.Open))
{
cn.Open();
}
// create command
using (var cmd = new SqlCommand(query, cn))
{
try
{
// define parameters and their values
cmd.Parameters.Add("@LogID", SqlDbType.UniqueIdentifier).Value = logId;
cmd.Parameters.Add("@LogDate", SqlDbType.DateTime).Value = DateTime.Now;
if (ascendId != null)
{
cmd.Parameters.Add("@AscendId", SqlDbType.VarChar, 24).Value = ascendId;
}
else
{
cmd.Parameters.Add("@AscendId", SqlDbType.VarChar, 24).Value = DBNull.Value;
}
cmd.Parameters.Add("@MasterDataId", SqlDbType.VarChar, 50).Value = masterDataId;
cmd.Parameters.Add("@MasterDataType", SqlDbType.VarChar, 50).Value = masterDataType;
if (ex == null)
{
cmd.Parameters.Add("@Status", SqlDbType.VarChar, 50).Value = status.ToString();
}
else
{
cmd.Parameters.Add("@Status", SqlDbType.VarChar, 50).Value = "2";
}
if (ex != null)
{
cmd.Parameters.Add("@Details", SqlDbType.VarChar, -1).Value = ex.Message;
if (ex.StackTrace != null)
{
cmd.Parameters.Add("@StackTrace", SqlDbType.VarChar, -1).Value =
ex.StackTrace;
}
else
{
cmd.Parameters.Add("@StackTrace", SqlDbType.VarChar, -1).Value = DBNull.Value;
}
}
else
{
cmd.Parameters.Add("@Details", SqlDbType.VarChar, -1).Value = "Success";
cmd.Parameters.Add("@StackTrace", SqlDbType.VarChar, -1).Value = DBNull.Value;
}
if (!string.IsNullOrEmpty(request))
{
cmd.Parameters.Add("@Request", SqlDbType.VarChar, -1).Value = request;
}
else
{
cmd.Parameters.Add("@Request", SqlDbType.VarChar, -1).Value = DBNull.Value;
}
if (!string.IsNullOrEmpty(requestRecieved))
{
cmd.Parameters.Add("@RequestRecieved", SqlDbType.VarChar, -1).Value = requestRecieved;
}
else
{
cmd.Parameters.Add("@RequestRecieved", SqlDbType.VarChar, -1).Value = DBNull.Value;
}
if (isError)
{
cmd.Parameters.Add("@IsError", SqlDbType.Bit).Value = 1;
}
else
{
cmd.Parameters.Add("@IsError", SqlDbType.Bit).Value = 0;
}
// open connection, execute INSERT, close connection
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
// Do not want to throw an error if something goes wrong logging
}
}
}
return logId;
}
发生记录问题的我的方法：
My Method where the logging issues occur:
public static void CallInsertTruckService(string id, string code, string vinNumber, string licPlateNo)
{
Logger.WriteToSLXLog(id, code, MasterDataType.TruckType, 4, "1", "", null, false);
try
{
var truckList = new TruckList();
var truck = new Truck();
truck.TruckId = code;
if (!string.IsNullOrEmpty(vinNumber))
{
truck.VIN = vinNumber;
}
else
{
truck.VIN = "";
}
if (!string.IsNullOrEmpty(licPlateNo))
{
truck.Tag = licPlateNo;
}
else
{
truck.Tag = "";
}
if (!string.IsNullOrEmpty(code))
{
truck.BackOfficeTruckId = code;
}
truckList.Add(truck);
Logger.WriteToSLXLog(id, code, MasterDataType.TruckType, 4, "2", "", null, false);
if (truckList.Any())
{
// Call SLX web service
using (var client = new WebClient())
{
var uri = SmartLogixConstants.LocalSmartLogixIntUrl;
uri += "SmartLogixApi/PushTruck";
client.Headers.Clear();
client.Headers.Add("content-type", "application/json");
client.Headers.Add("FirestreamSecretToken", SmartLogixConstants.FirestreamSecretToken);
var serialisedData = JsonConvert.SerializeObject(truckList, new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Serialize
});
// HTTP POST
var response = client.UploadString(uri, serialisedData);
var result = JsonConvert.DeserializeObject<SmartLogixResponse>(response);
Logger.WriteToSLXLog(id, code, MasterDataType.TruckType, 4, "3", "", null, false);
if (result == null || result.ResponseStatus != 1)
{
// Something went wrong
throw new ApplicationException("Error in SLX");
}
Logger.WriteToSLXLog(id, code, MasterDataType.TruckType, result.ResponseStatus, serialisedData,
null, null, false);
}
}
}
catch (Exception ex)
{
Logger.WriteToSLXLog(id, code, MasterDataType.TruckType, 4, "4", "", null, false);
throw;
}
finally
{
Logger.WriteToSLXLog(id, code, MasterDataType.TruckType, 4, "5", "", null, false);
}
}
您可以看到我在整个过程中添加了一些日志语句方法。如果未引发任何异常，则除catch块中的所有语句外，所有这些日志语句均成功。如果引发异常，那么它们都不成功。对于其中大多数值，无论是否存在异常，它们的值都完全相同，因此我知道传递值不是问题。我认为发生了一些奇怪的事情，这会导致回滚或其他事情，但是我在这里没有使用事务或任何事情。 DLL的最后一件事是通过SQL CLR运行，这就是为什么我在连接字符串中使用 context connection = true。
As you can see I have added several log statements throughout the method. All of these log statements except the one in the catch block are successful if no exception is thrown. If an exception is thrown then none of them are successful. For most of them the values are exactly the same whether or not there is an exception so I know its not an issue with the values being passed. I am thinking something weird is happening that causes a rollback or something, but I am not using a transaction or anything here. One last thing this DLL is being run through the SQL CLR which is why I am using "context connection=true" for my connection string.
在此先感谢。
编辑：
我尝试将以下内容添加为连接字符串，但是尝试打开时出现异常。现在显示另一个会话正在使用事务上下文的连接。我认为这与我通过触发器调用此SQL CLR过程有关。我尝试的连接字符串是
I tried adding the following as my connection string but I get an exception when trying to .Open the connection now that says "Transaction context in use by another session". I am thinking this has to do with me calling this SQL CLR procedure through a trigger. The connection string I tried is
connectionString = "Trusted_Connection=true;Data Source=(local)\\AARONSQLSERVER;Initial Catalog=Demo409;Integrated Security=True;";
这也是触发条件：
CREATE TRIGGER [dbo].[PushToSLXOnVehicleInsert]
ON [dbo].[Vehicle] AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @returnValue int
DECLARE @newLastModifiedDate datetime = null
DECLARE @currentId bigint = null
DECLARE @counter int = 0;
DECLARE @maxCounter int
DECLARE @currentCode varchar(24) = null
DECLARE @currentVinNumber varchar(24)
DECLARE @currentLicPlateNo varchar(30)
declare @tmp table
(
id int not null
primary key(id)
)
insert @tmp
select VehicleID from INSERTED
SELECT @maxCounter = Count(*) FROM INSERTED GROUP BY VehicleID
BEGIN TRY
WHILE (@counter < @maxCounter)
BEGIN
select top 1 @currentId = id from @tmp
SELECT @currentCode = Code, @currentVinNumber = VINNumber, @currentLicPlateNo = LicPlateNo FROM INSERTED WHERE INSERTED.VehicleID = @currentId
if (@currentId is not null)
BEGIN
EXEC dbo.SLX_CallInsertTruckService
@id = @currentId,
@code = @currentCode,
@vinNumber = @currentVinNumber,
@licPlateNo = @currentLicPlateNo
END
delete from @tmp where id = @currentId
set @counter = @counter + 1;
END
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
IF (@ErrorMessage like '%Error in SLX%')
BEGIN
SET @ErrorMessage = 'Error in SLX. Please contact SLX for more information.'
END
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
END
GO
推荐答案
这里的主要问题是从触发器中调用了SQLCLR存储过程。触发器始终在事务的上下文中运行（以将其绑定到启动触发器的DML操作）。触发器还将 XACT_ABORT 隐式设置为 ON ，如果发生 any 错误，则取消交易。这就是为什么在引发异常时没有日志记录语句会持久存在的原因：事务会自动回滚，并在同一会话中进行任何更改，包括日志记录语句（因为 Context Connection） 是同一会话），以及原始的DML语句。
The main issue here is that the SQLCLR Stored Procedure is being called from within a Trigger. A Trigger always runs within the context of a Transaction (to bind it to the DML operation that initiated the Trigger). A Trigger also implicitly sets XACT_ABORT to ON which cancels the Transaction if any error occurs. This is why none of the logging statements persist when an exception is thrown: the Transaction is auto-rolled-back, taking with it any changes made in the same Session, including the logging statements (because the Context Connection is the same Session), as well as the original DML statement.
您有三个相当简单的选择，尽管它们为您提供了一个整体架构问题，或者解决一些紧迫的问题以及更大的架构问题的解决方案，但不是那么困难，而是需要更多的工作。首先，这是三个简单的选项：
You have three fairly simple options, though they leave you with an overall architectural problem, or a not-so-difficult-but-a-little-more-work option that solves the immediate issue as well as the larger architectural problem. First, the three simple options:
-
您可以执行
SET XACT_ABORT OFF;在触发器的开头。这将使TRY ... CATCH构造可以按您期望的那样工作。但是，这也将责任转移给您，发出ROLLBACK（通常在CATCH块中），除非您希望原始DML语句无论如何都成功，即使Web Service调用和记录失败。当然，如果发出ROLLBACK，则即使Web Service仍注册所有成功的调用（如果有的话），任何日志记录语句也不会持久。
You can execute
SET XACT_ABORT OFF;at the beginning of the Trigger. This will allow theTRY ... CATCHconstruct to work as you are expecting it to. HOWEVER, this also shifts the responsibility to you issue aROLLBACK(usually in theCATCHblock), unless you want the original DML statement to succeed no matter what, even if the Web Service calls and logging fail. Of course, if you issue aROLLBACK, then none of the logging statements will persist, even if the Web Service still registers all of the calls that were successful, if any were.
您可以单独保留 SET XACT_ABORT 并使用到SQL Server的常规/外部连接。常规连接将是一个完全独立的连接和会话，因此它可以独立于事务运行。与 SET XACT_ABORT OFF; 选项不同，这将允许触发器正常运行（即，任何错误都会回滚触发器本身以及原始的DML语句），同时仍然允许日志记录 INSERT 语句持久化（因为它们是在本地事务之外进行的。）
You can leave SET XACT_ABORT alone and use a regular / external connection to SQL Server. A regular connection will be an entirely separate Connection and Session, hence it can operate independantly with regards to the Transaction. Unlike the SET XACT_ABORT OFF; option, this would allow the Trigger to operate "normally" (i.e. any error would roll-back any changes made natively in the Trigger as well as the original DML statement) while still allowing the logging INSERT statements to persist (since they were made outside of the local Transaction).
您已经在调用Web服务，因此程序集已经具有执行此操作所需的权限，而无需进行任何其他更改。您只需要使用正确的连接字符串（您的语法中有一些错误），可能类似于以下内容：
You are already calling a Web Service so the Assembly already has the necessary permissions to do this without making any additional changes. You just need to use a proper connection string (there are a few errors in your syntax), probably something along the lines of:
connectionString = @"Trusted_Connection=True; Server=(local)\AARONSQLSERVER; Database=Demo409; Enlist=False;";
Enlist = False; 部分（滚动到最右边）非常重要：没有它，您将继续收到 另一个会话正在使用的交易上下文错误。
The "Enlist=False;" part (scroll to the far right) is very important: without it you will continue to get the "Transaction context in use by another session" error.
如果您想坚持使用 Context Connection （速度稍快一点），并允许在外部服务回滚原始DML语句和所有日志记录语句，同时忽略来自Web Service甚至日志 INSERT 语句的错误，那么您根本无法在 CallInsertTruckService 的 catch 块中重新抛出异常。您可以改为设置一个变量以指示返回码。由于这是一个存储过程，因此它可以返回 SqlInt32 而不是 void 。然后，可以通过声明 INT 变量并将其包含在 EXEC 调用中来获得该值，如下所示：
If you want to stick with the Context Connection (it is a little faster) and allow for any errors outside of the Web Service to roll-back the original DML statement and all logging statements, while ignoring errors from the Web Service, or even from the logging INSERT statements, then you can simply not re-throw the exception in the catch block of CallInsertTruckService. You could instead set a variable to indicate a return code. Since this is a Stored Procedure, it can return SqlInt32 instead of void. Then you can get that value by declaring an INT variable and including it in the EXEC call as follows:
EXEC @ReturnCode = dbo.SLX_CallInsertTruckService ...;
只需在 CallInsertTruckService 并将其初始化为 0 。然后在 catch 块中将其设置为其他值。在该方法的末尾，包括 return _ReturnCode; 。
Just declare a variable at the top of CallInsertTruckService and initialize it to 0. Then set it to some other value in the catch block. And at the end of the method, include a return _ReturnCode;.
话虽如此，无论您选择哪种选择，您仍然面临两个相当大的问题：
That being said, no matter which of those choices you pick, you are still left with two fairly large problems:
-
Web服务调用阻止了DML语句及其系统启动的事务。交易的开放时间将长于应有的时间，这至少会增加与
Vehicle表有关的封锁。虽然我当然是倡导通过SQLCLR进行Web服务调用的人，但我强烈建议不要在触发器中这样做。
The DML statement and its system-initiated Transaction are impeded by the Web Service calls. The Transaction will be left open for much longer than it should be, and this could at the very least increase blocking related to the
VehicleTable. While I am certainly an advocate of doing Web Service calls via SQLCLR, I would strongly recommend against doing so within a Trigger.
如果每个 VehicleID 将会被跳过，即使不是这样（上面的选项3将继续处理 @tmp 中的行），那么至少出现错误的那一行
If each VehicleID that is inserted should be passed over to the Web Service, then if there is an error in one Web Service call, the remaining VehicleIDs will be skipped, and even if they aren't (option # 3 above would continue processing the rows in @tmp) then at the very least the one that just had the error won't ever be retried later.
因此，理想的方法可以解决这两个非常重要的问题最初的日志记录问题是转移到断开连接的异步模型。您可以设置一个队列表来保存要根据每个 INSERT 处理的Vehile信息。触发器将执行以下操作：
Hence the ideal approach, which solves these two rather important issues as well the initial logging issue, is to move to a disconnected asynchronous model. You can set up a queue table to hold the Vehile info to process based on each INSERT. The Trigger would do a simple:
INSERT INTO dbo.PushToSLXQueue (VehicleID, Code, VINNumber, LicPlateNo)
SELECT VehicleID, Code, VINNumber, LicPlateNo
FROM INSERTED;
然后创建一个存储过程，该过程从队列表中读取一个项目，调用Web服务，如果成功，然后从队列表中删除该条目。从SQL Server代理作业安排此存储过程每10分钟运行一次或类似的操作。
Then create a Stored Procedure that reads an item from the queue table, calls the Web Service, and if successful, then deletes that entry from the queue table. Schedule this Stored Procedure from a SQL Server Agent job to run every 10 minutes or something like that.
如果有永远不会处理的记录，则可以添加 RetryCount 列到队列表中，默认将其设置为0，并且在Web服务出现错误时，增加 RetryCount 删除行。然后，您可以更新获取要处理的条目 SELECT 查询，以包括 WHERE RetryCount< 5 或您要设置的任何限制。
If there are records that will never process, then you can add a RetryCount column to the queue table, default it to 0, and upon the Web Service getting an error, increment RetryCount instead of removing the row. Then you can update the "get entry to process" SELECT query to include WHERE RetryCount < 5 or whatever limit you want to set.
这里有一些问题，各种影响程度：
There are a few issues here, with various levels of impact:
-
为什么
id是<$ c T-SQL代码中的$ c> BIGINT 还是C＃代码中的字符串？
Why is
idaBIGINTin the T-SQL code yet a string in the C# code?
仅供参考，<$ c与使用实际的 CURSOR 相比，$ c> WHILE（@counter< @maxCounter）循环效率低下并且容易出错。我会摆脱 @tmp 表变量和 @maxCounter 。
Just FYI, the WHILE (@counter < @maxCounter) loop is inefficient and error prone compared to using an actual CURSOR. I would get rid of the @tmp Table Variable and @maxCounter.
至少将 SELECT @maxCounter = COUNT（*）从按车辆ID插入的分组更改为 SET @maxCounter = @ @ROWCOUNT; ;-)。但是最好换成真正的 CURSOR 。
At the very least change SELECT @maxCounter = Count(*) FROM INSERTED GROUP BY VehicleID to be just SET @maxCounter = @@ROWCOUNT; ;-). But swapping out for a real CURSOR would be best.
如果 CallInsertTruckService（字符串ID，字符串代码，字符串vinNumber，字符串licPlateNo）签名是用 [SqlProcedure（）] 装饰的实际方法。应该使用 SqlString 而不是 string 。使用 SqlString .Value 属性从每个参数获取本机 string 值。 code>参数。然后，可以使用 [SqlFacet（）] 属性设置适当的大小，如下所示：
If the CallInsertTruckService(string id, string code, string vinNumber, string licPlateNo) signature is the actual method decorated with [SqlProcedure()], then you really should be using SqlString instead of string. Get the native string value from each parameter using the .Value property of the SqlString parameter. You can then set the proper size using the [SqlFacet()] attribute as follows:
[SqlFacet(MaxSize=24)] SqlString vinNumber
有关一般使用SQLCLR的更多信息，请参见我在SQL Server Central上针对该主题撰写的系列文章： SQLCLR的阶梯（需要免费注册才能读取该站点上的内容）。
For more info on working with SQLCLR in general, please see the series that I am writing on this topic over at SQL Server Central: Stairway to SQLCLR (free registration is required to read content on that site).
这篇关于在触发器中执行的方法中发生异常时，日志不持久的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
