SQL Server 2005/2008 中的异步触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/766761/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Asynchronous Triggers in SQL Server 2005/2008
提问by Jose Basilio
I have triggers that manipulate and insert a lot of data into a Change trackingtable for audit purposes on every insert, update and delete.
我有触发器，可以在每次插入、更新和删除时操作并将大量数据插入到更改跟踪表中，以便进行审计。
This trigger does its job very well, by using it we are able to log the desired oldvalues/newvalues as per the business requirements for every transaction.
这个触发器很好地完成了它的工作，通过使用它，我们能够根据每个事务的业务需求记录所需的旧值/新值。
However in somecases where the source table has a lot columns, it can take up to 30 seconds for the transaction to complete which is unacceptable.
但是，在某些情况下，源表有很多列，事务可能需要 30 秒才能完成，这是不可接受的。
Is there a way to make the trigger run asynchronously?Any examples.
有没有办法让触发器异步运行？任何例子。
采纳答案by Sean Reilly
You can't make the trigger run asynchronously, but you could have the trigger synchronously send a message to a SQL Service Brokerqueue. The queue can then be processed asynchronously by a stored procedure.
您不能让触发器异步运行，但可以让触发器同步向SQL Service Broker队列发送消息。然后可以通过存储过程异步处理队列。
回答by Mladen Prajdic
these articles show how to use service broker for async auditing and should be useful:
这些文章展示了如何使用服务代理进行异步审计，应该很有用：
Centralized Asynchronous Auditing with Service Broker
Service Broker goodies: Cross Server Many to One (One to Many) scenario and How to troubleshoot it
回答by moander
SQL Server 2014 introduced a very interesting feature called Delayed Durability. If you can tolerate loosing a few rows in case of an catastrophic event, like a server crash, you could really boost your performance in schenarios like yours.
SQL Server 2014 引入了一个非常有趣的特性，称为Delayed Durability。如果您可以容忍在发生灾难性事件（例如服务器崩溃）时丢失几行，那么您就可以真正提高在像您这样的场景中的性能。
Delayed transaction durability is accomplished using asynchronous log writes to disk. Transaction log records are kept in a buffer and written to disk when the buffer fills or a buffer flushing event takes place. Delayed transaction durability reduces both latency and contention within the system
延迟事务持久性是使用异步日志写入磁盘来实现的。事务日志记录保存在缓冲区中，并在缓冲区填满或缓冲区刷新事件发生时写入磁盘。延迟事务持久性减少了系统内的延迟和争用
The database containing the table must first be altered to allow delayed durability.
必须首先更改包含该表的数据库以允许延迟持久性。
ALTER DATABASE dbname SET DELAYED_DURABILITY = ALLOWED
Then you could control the durability on a per-transaction basis.
然后，您可以在每个事务的基础上控制持久性。
begin tran
insert into ChangeTrackingTable select * from inserted
commit with(DELAYED_DURABILITY=ON)
The transaction will be commited as durable if the transaction is cross-database, so this will only work if your audit table is located in the same database as the trigger.
如果事务是跨数据库的，则事务将被提交为持久事务，因此这仅在您的审计表与触发器位于同一数据库中时才有效。
There is also a possibility to alter the database as forced instead of allowed. This causes all transactions in the database to become delayed durable.
也有可能将数据库更改为强制而不是允许。这会导致数据库中的所有事务变为延迟持久。
ALTER DATABASE dbname SET DELAYED_DURABILITY = FORCED
For delayed durability, there is no difference between an unexpected shutdown and an expected shutdown/restart of SQL Server. Like catastrophic events, you should plan for data loss. In a planned shutdown/restart some transactions that have not been written to disk may first be saved to disk, but you should not plan on it. Plan as though a shutdown/restart, whether planned or unplanned, loses the data the same as a catastrophic event.
对于延迟持久性，SQL Server 的意外关闭和预期关闭/重新启动之间没有区别。与灾难性事件一样，您应该为数据丢失做好准备。在计划的关闭/重启中，一些尚未写入磁盘的事务可能会首先保存到磁盘，但您不应计划它。计划好停机/重启，无论是计划内的还是计划外的，都会像灾难性事件一样丢失数据。
This strange defect will hopefully be addressed in a future release, but until then it may be wise to make sure to automatically execute the 'sp_flush_log' procedure when SQL server is restarting or shutting down.
这个奇怪的缺陷有望在未来的版本中得到解决，但在那之前，确保在 SQL 服务器重新启动或关闭时自动执行“sp_flush_log”过程可能是明智的。
回答by Jesús López
To perform asynchronous processing you can use Service Broker, but it isn't the only option, you can also use CLR objects.
要执行异步处理，您可以使用 Service Broker，但这不是唯一的选择，您还可以使用 CLR 对象。
The following is an example of an stored procedure (AsyncProcedure) that asynchronous calls another procedure (SyncProcedure):
以下是异步调用另一个过程 (SyncProcedure) 的存储过程 (AsyncProcedure) 示例：
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Runtime.Remoting.Messaging;
using System.Diagnostics;
public delegate void AsyncMethodCaller(string data, string server, string dbName);
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void AsyncProcedure(SqlXml data)
{
AsyncMethodCaller methodCaller = new AsyncMethodCaller(ExecuteAsync);
string server = null;
string dbName = null;
using (SqlConnection cn = new SqlConnection("context connection=true"))
using (SqlCommand cmd = new SqlCommand("SELECT @@SERVERNAME AS [Server], DB_NAME() AS DbName", cn))
{
cn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
server = reader.GetString(0);
dbName = reader.GetString(1);
}
}
methodCaller.BeginInvoke(data.Value, server, dbName, new AsyncCallback(Callback), null);
//methodCaller.BeginInvoke(data.Value, server, dbName, null, null);
}
private static void ExecuteAsync(string data, string server, string dbName)
{
string connectionString = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI", server, dbName);
using (SqlConnection cn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand("SyncProcedure", cn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@data", SqlDbType.Xml).Value = data;
cn.Open();
cmd.ExecuteNonQuery();
}
}
private static void Callback(IAsyncResult ar)
{
AsyncResult result = (AsyncResult)ar;
AsyncMethodCaller caller = (AsyncMethodCaller)result.AsyncDelegate;
try
{
caller.EndInvoke(ar);
}
catch (Exception ex)
{
// handle the exception
//Debug.WriteLine(ex.ToString());
}
}
}
It uses asynchronous delegates to call SyncProcedure:
它使用异步委托来调用 SyncProcedure：
CREATE PROCEDURE SyncProcedure(@data xml)
AS
INSERT INTO T(Data) VALUES (@data)
Example of calling AsyncProcedure:
调用 AsyncProcedure 的示例：
EXEC dbo.AsyncProcedure N'<doc><id>1</id></doc>'
Unfortunatelly, the assembly requires UNSAFE permission.
不幸的是，程序集需要 UNSAFE 许可。
回答by GordyII
I wonder if you could tag a record for the change tracking by inserting into a "too process" table including who did the change etc etc.
我想知道您是否可以通过插入“太进程”表（包括谁进行了更改等）来标记更改跟踪的记录。
Then another process could come along and copy the rest of the data on a regular basis.
然后另一个进程可能会出现并定期复制其余数据。
回答by dkretz
There's a basic conflict between "does its job very well" and "unacceptable", obviously.
显然，“做得很好”和“不可接受”之间存在基本冲突。
It sounds to me that you're trying to use triggers the same way you would use events in an OO procedural application, which IMHO doesn't map.
在我看来，您正在尝试以与在 OO 程序应用程序中使用事件相同的方式使用触发器，恕我直言，它没有映射。
I would call any trigger logic that takes 30 seconds - no, more that 0.1 second - as disfunctional. I think you really need to redesign your functionality and do it some other way. I'd say "if you want to make it asynchronous", but I don't think this design makes sense in any form.
我会称任何需要 30 秒的触发逻辑 - 不，超过 0.1 秒 - 都无法正常工作。我认为您确实需要重新设计您的功能并以其他方式进行。我会说“如果你想让它异步”，但我认为这种设计在任何形式上都没有意义。
As far as "asynchronous triggers", the basic fundamental conflict is that you could never include such a thing between BEGIN TRAN and COMMIT TRAN statements because you've lost track of whether it succeeded or not.
至于“异步触发器”，基本的根本冲突是您永远不能在 BEGIN TRAN 和 COMMIT TRAN 语句之间包含这样的东西，因为您已经忘记了它是否成功。
回答by Arvo
Create history table(s). While updating (/deleting/inserting) main table, insert old values of record (deleted pseudo-table in trigger) into history table; some additional info is needed too (timestamp, operation type, maybe user context). New values are kept in live table anyway.
创建历史表。在更新（/删除/插入）主表的同时，将记录的旧值（触发器中删除的伪表）插入到历史表中；还需要一些额外的信息（时间戳、操作类型，可能是用户上下文）。无论如何，新值都保留在实时表中。
This way triggers run fast(er) and you can shift slow operations to log viewer (procedure).
这种方式触发器运行得很快（呃），您可以将慢速操作转移到日志查看器（过程）。
回答by gbn
I suspect that your trigger is of of these generic csv/text generating triggers designed to log all changes for all table in one place. Good in theory (perhaps...), but difficult to maintain and use in practice.
我怀疑您的触发器属于这些通用 csv/文本生成触发器，旨在将所有表的所有更改都记录在一个地方。理论上很好（也许......），但在实践中难以维护和使用。
If you could run asynchronously (which would still require storing data somewhere for logging again later), then you are not auditing and neither do have history to use.
如果您可以异步运行（这仍然需要将数据存储在某处以便稍后再次记录），那么您就没有进行审计，也没有可以使用的历史记录。
Perhaps you could look at the trigger execution plan and see what bit is taking the longest?
也许您可以查看触发器执行计划，看看哪个位花费的时间最长？
Can you change how you audit, say, to per table? You could split the current log data into the relevant tables.
你能改变你的审计方式吗，比如，每张表？您可以将当前日志数据拆分到相关表中。
回答by Karl
Not that I know of, but are you inserting values into the Audit table that also exist in the base table? If so, you could consider tracking just the changes. Therefore an insert would track the change time, user, extra and a bunch of NULLs (in effect the before value). An update would have the change time, user etc and the before value of the changed column only. A delete has the change at, etc and all values.
不是我所知道的，但是您是否将值插入也存在于基表中的审计表中？如果是这样，您可以考虑仅跟踪更改。因此，插入将跟踪更改时间、用户、额外和一堆 NULL（实际上是之前的值）。更新将仅具有更改时间、用户等和更改列的之前值。删除具有更改 at 等和所有值。
Also, do you have an audit table per base table or one audit table for the DB? Of course the later can more easily result in waits as each transaction tries to write to the one table.
另外，对于每个基表，您是否有一个审计表或一个数据库审计表？当然，后者更容易导致等待，因为每个事务都试图写入一个表。
