TSQL：在提交时触发 [英] TSQL: Trigger on Commit
问题描述
我们有一个后台系统，该系统通过 MSDTC 存储过程，应用程序将插入标头，然后插入详细信息。
We have a back office system that inserts invoice information into an SQL database via a MSDTC stored procedure, the application inserts header, then the detail information.
我已经设置了 CLR 在标题表上触发时触发
I have set up a CLR trigger on the header table that fires when a record is inserted.
我遇到的问题是触发器在 COMMIT TRANSACTION 之前触发。这意味着详细信息可能不被填充，这是触发的过程所必需的。另外，如果系统触发 ROLLBACK TRANSACTION 触发器已经触发。
The problem I am having is that the trigger is firing before the COMMIT TRANSACTION meaning that the details information might not be populated, which is required by the process that is triggered. Also if the system fires a ROLLBACK TRANSACTION the trigger has already fired.
我知道不能将触发器分配给 COMMIT ，但想知道是否还有其他想法。
I understand that triggers can't be assigned to a COMMIT, but was wondering if any one had any other thoughts.
我偶然发现了一个针对Oracle的建议解决方案，您在其中创建了Materlised View会更新 ON COMMIT ，并尝试找到与TSQL等效的索引视图，但没有运气实现。
I stumbled across a suggested solution for oracle where you create a Materlised View that updates ON COMMIT, and tried to find the TSQL equivalent which is Indexed Views, but had no luck implementing it.
总结：
是否有可能使用索引视图触发 COMMIT TRANSACTION 吗？
CLR 触发代码
CLR Trigger Code
将JMS消息发送到队列，由 Sonic处理ESB 。
Sends a JMS Message to a queue, to be processed by Sonic ESB.
[Microsoft.SqlServer.Server.SqlTrigger(Name = "InvoiceTrigger", Target = "Table", Event = "FOR INSERT")]
public static void InvoiceTrigger()
{
//Declare Connection vairables
string connectionURL, connectionDomain, connectionUser, connectionPassword, connectionQueue;
//Constant
connectionUser = "user";
connectionPassword = "pass";
connectionQueue = "Queue";
//Local Environment
connectionURL = "tcp://IP:2506";
connectionDomain = "Domain1";
//Create connection sonic domain
SonicSend send = new SonicSend(connectionURL, connectionDomain, connectionUser, connectionPassword, connectionQueue);
//Send Test message to pipe
SqlCommand command;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader reader;
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT LSH_LINKCODE, DOC_CODE, LSH_DOCNUM FROM INSERTED;", connection);
reader = command.ExecuteReader();
if (reader.HasRows)
{
string xml;
char cr = (char)13;
int i = 0;
while (reader.Read())
{
xml = @"<Invoice action='insert'>";
xml += "<LinkCode>" + reader.GetString(0).ToString() + "</LinkCode>";
xml += "<DocumentCode>" + reader.GetString(1).ToString() + "</DocumentCode>";
xml += "<DocumentNumber>" + reader.GetString(2).ToString() + "</DocumentNumber>";
xml += @"</Invoice>";
i++;
send.testJMSsend(xml);
}
}
reader.Close();
}
break;
}
}
推荐答案
在发表有用的评论指出不可能在触发器中使用提交之后。
After helpful comments stating that utilizing the commit in a trigger wasn't possible.
我回顾了由触发器启动的ESB流程，现在查询了该流程具有传递的信息的数据，这已经解决了我的问题，因为 BEGIN TRANSACTION 创建了对数据的锁定，直到出现以下情况查询才返回结果 COMMIT TRANSACTION ，因此这意味着直到查询返回数据，该过程才会继续，因此将始终填充发票详细信息。
I reviewed the ESB process that was kicked off by trigger, the process now queries the data with the passed information, this has fixed my problem due to the fact that the BEGIN TRANSACTION creates a lock on the data the query doesn't return results until there has been COMMIT TRANSACTION, thus meaning the process won't continue until the query returns data, so the Invoice details will always be populated.
此外，如果发生 ROLLBACK TRANSACTION ，则该过程将按预期方式抛出错误。
Also if there was a ROLLBACK TRANSACTION, the process would time out throwing an error as expected.
这篇关于TSQL：在提交时触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
