SQL Server从触发器触发异步更新？
如果用户将行插入表中，我希望 SQL Server 执行一些额外的处理 - 但不是在用户事务的上下文中。
例如，用户授予对文件夹的读访问权限：
UPDATE Folders SET ReadAccess = 1
WHERE FolderID = 7
就用户而言，我希望这是原子操作的结束。实际上，我现在必须找到所有子文件和文件夹并为它们提供 ReadAccess。
EXECUTE SynchronizePermissions
这是一个可能很长的操作（超过 2 秒）。我希望这个冗长的操作“稍后”发生。它可能会在 0 秒后发生，在碳单元有机会考虑它之前，异步更新已完成。
我如何在需要时异步运行此所需操作（即触发）？
理想的是：
CREATE TRIGGER dbo.Folders FOR INSERT, UPDATE, DELETE AS
EXECUTEASYNCHRONOUS SynchronizePermissions
或者
CREATE TRIGGER dbo.Folders FOR INSERT, UPDATE, DELETE AS
EXECUTE SynchronizePermissions WITH(ASYNCHRONOUS)
现在这作为触发器发生：
CREATE TRIGGER dbo.Folders FOR INSERT, UPDATE, DELETE AS
EXECUTE SynchronizePermissions
并且用户每次对Folders表进行更改时都被迫等待3秒。
我考虑过在用户上创建一个每分钟运行一次的计划任务，并检查PermissionsNeedSynchronizing标志：
CREATE TRIGGER dbo.Folders FOR INSERT, UPDATE, DELETE AS
UPDATE SystemState SET PermissionsNeedsSynchronizing = 1
计划任务二进制文件可以检查此标志，运行如果标志打开：
DECLARE @FlagValue int
SET @FlagValue = 0;
UPDATE SystemState SET @FlagValue = PermissionsNeedsSynchronizing+1
WHERE PermissionsNeedsSynchronizing = 1
IF @FlagValue = 2
BEGIN
EXECUTE SynchronizePermissions
UPDATE SystemState SET PermissionsNeedsSynchronizing = 0
WHERE PermissionsNeedsSynchronizing = 2
END
计划任务的问题是： - 最快运行一次是每 60 秒 - 它是一个轮询解决方案 - 它需要一个可执行文件
我更喜欢的是 SQL Server 可以触发计划任务的方式：
CREATE TRIGGER dbo.Folders FOR INSERT, UPDATE, DELETE AS
EXECUTE SynchronizePermissionsAsychronous
CREATE PROCEDURE dbo.SynchronizePermissionsAsychronous AS
EXECUTE sp_ms_StartWindowsScheduledTask @taskName="SynchronousPermissions"
问题是： - 没有 sp_ms_StartWinodowsScheduledTask 系统存储过程
所以我正在寻找更好的解决方案的想法。
更新：前面的例子是一个问题，五年来一直没有好的解决方案。 3 年前的一个问题，没有好的解决方案是一个表，我需要在插入/更新后更新元数据列。在线事务处理中元数据的计算时间太长，但我可以接受它在 3 或 5 秒后出现：
CREATE TRIGGER dbo.UpdateFundsTransferValues FOR INSERT, UPDATE AS
UPDATE FundsTransfers
SET TotalOrderValue = (SELECT ....[snip]....),
TotalDropValue = (SELECT ....,[snip]....)
WHERE FundsTransfers.FundsTransferID IN (
SELECT i.FundsTransferID
FROM INSERTED i
)
而我今天遇到的问题是在过渡插入一行后异步更新某些元数据的方法或修改：
CREATE TRIGGER dbo.UpdateCDRValue FOR INSERT, UPDATE AS
UPDATE LCDs
SET CDRValue = (SELECT ....[snip]....)
WHERE LCDs.LCDGUID IN (
SELECT i.LCDGUID
FROM INSERTED i
)
更新 2：我考虑过创建一个本机或托管 dll 并将其用作扩展存储过程。问题是：
- 你不能编写二进制脚本，
- 我现在可以这样做
If a user inserts rows into a table, i would like SQL Server to perform some additional processing - but not in the context of the user's transaction.
e.g. The user gives read access to a folder:
UPDATE Folders SET ReadAccess = 1
WHERE FolderID = 7
As far as the user is concerned i want that to be the end of the atomic operation. In reality i have to now go find all child files and folders and give them ReadAccess.
EXECUTE SynchronizePermissions
This is a potentially lengthy operation (over 2s). i want this lengthy operation to happen "later". It can happen 0 seconds later, and before the carbon-unit has a chance to think about it the asynchronous update is done.
How can i run this required operation asychronously when it's required (i.e. triggered)?
The ideal would be:
CREATE TRIGGER dbo.Folders FOR INSERT, UPDATE, DELETE AS
EXECUTEASYNCHRONOUS SynchronizePermissions
or
CREATE TRIGGER dbo.Folders FOR INSERT, UPDATE, DELETE AS
EXECUTE SynchronizePermissions WITH(ASYNCHRONOUS)
Right now this happens as a trigger:
CREATE TRIGGER dbo.Folders FOR INSERT, UPDATE, DELETE AS
EXECUTE SynchronizePermissions
and the user is forced to wait the 3 seconds every time they make a change to the Folders table.
i've thought about creating a Scheduled Task on the user, that runs every minute, and check for an PermissionsNeedSynchronizing flag:
CREATE TRIGGER dbo.Folders FOR INSERT, UPDATE, DELETE AS
UPDATE SystemState SET PermissionsNeedsSynchronizing = 1
The scheduled task binary can check for this flag, run if the flag is on:
DECLARE @FlagValue int
SET @FlagValue = 0;
UPDATE SystemState SET @FlagValue = PermissionsNeedsSynchronizing+1
WHERE PermissionsNeedsSynchronizing = 1
IF @FlagValue = 2
BEGIN
EXECUTE SynchronizePermissions
UPDATE SystemState SET PermissionsNeedsSynchronizing = 0
WHERE PermissionsNeedsSynchronizing = 2
END
The problem with a scheduled task is:
- the fastest it can run is every 60 seconds
- it's suffers from being a polling solution
- it requires an executable
What i'd prefer is a way that SQL Server could trigger the scheduled task:
CREATE TRIGGER dbo.Folders FOR INSERT, UPDATE, DELETE AS
EXECUTE SynchronizePermissionsAsychronous
CREATE PROCEDURE dbo.SynchronizePermissionsAsychronous AS
EXECUTE sp_ms_StartWindowsScheduledTask @taskName="SynchronousPermissions"
The problem with this is:
- there is no sp_ms_StartWinodowsScheduledTask system stored procedure
So i'm looking for ideas for better solutions.
Update: The previous example is a problem, that has has no good solution, for five years now. A problem from 3 years ago, that has no good solution is a table that i need to update a meta-data column after an insert/update. The metadata takes too long to calculate in online transaction processing, but i am ok with it appearing 3 or 5 seconds later:
CREATE TRIGGER dbo.UpdateFundsTransferValues FOR INSERT, UPDATE AS
UPDATE FundsTransfers
SET TotalOrderValue = (SELECT ....[snip]....),
TotalDropValue = (SELECT ....,[snip]....)
WHERE FundsTransfers.FundsTransferID IN (
SELECT i.FundsTransferID
FROM INSERTED i
)
And the problem that i'm having today is a way to asychronously update some metadata after a row has been transitionally inserted or modified:
CREATE TRIGGER dbo.UpdateCDRValue FOR INSERT, UPDATE AS
UPDATE LCDs
SET CDRValue = (SELECT ....[snip]....)
WHERE LCDs.LCDGUID IN (
SELECT i.LCDGUID
FROM INSERTED i
)
Update 2: i've thought about creating a native, or managed, dll and using it as an extended stored procedure. The problem with that is:
- you can't script a binary
- i'm now allowed to do it
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
使用队列表，并让不同的后台进程从队列中取出内容并进行处理。根据定义，触发器本身是用户交易的一部分 - 这正是他们经常被劝阻的原因（或者至少人们被警告不要在触发器内使用昂贵的技术）。
Use a queue table, and have a different background process pick things up off the queue and process them. The trigger itself is by definition a part of the user's transaction - this is precisely why they are often discouraged (or at least people are warned to not use expensive techniques inside triggers).
创建一个 SQL 代理作业并使用 sp_start_job 运行它..它不应该等待完成
但是您需要适当的权限才能运行作业
这种方法的问题是，如果作业已经在运行，则在完成之前无法启动
否则，请使用 Aaron 建议的队列表，它更干净、更好
Create a SQL Agent job and run it with sp_start_job..it shouldn't wait for completion
However you need the proper permission to run jobs
The problem with this approach is that if the job is already running it can't be started until it is finished
Otherwise go with the queue table that Aaron suggested, it is cleaner and better
我们不久前遇到了这个问题，我找到了一个效果很好的解决方案。我确实有一个进程在后台运行 - 但就像您一样，我不希望它每 60 秒轮询一次。
步骤如下：
(1) 我们的触发器本身不运行数据库更新。它只是将一个“标志文件”放入后台进程监视的文件夹中。
(2) 后台进程使用 Windows 更改通知（这是非常酷的部分，因为您不必轮询文件夹 - 您的进程会休眠，直到 Windows 通知它文件已更改）出现）。每当 Windows 唤醒后台进程时，它就会运行数据库更新。然后它会删除标志文件，再次进入睡眠状态，并告诉 Windows 在文件夹中出现另一个文件时将其唤醒。
这与您所描述的完全一样：触发的更新在主数据库事件之后不久运行，瞧，用户不必等待额外的几秒钟。我就是喜欢它。
您不一定需要编译自己的可执行文件来执行此操作：许多脚本语言都可以使用 Windows 更改通知。我用 Perl 编写了后台进程，只花了几分钟就让它运行起来。
We came across this problem some time ago, and I figured out a solution that works beautifully. I do have a process running in the background-- but just like you, I didn't want it to have to poll every 60 seconds.
Here are the steps:
(1) Our trigger doesn't run the db update itself. It merely throws a "flag file" into a folder that is monitored by the background process.
(2) The background process monitors that folder using Windows Change Notification (this is the really cool part, because you don't have to poll the folder-- your process sleeps until Windows notifies it that a file has appeared). Whenever the background process is awoken by Windows, it runs the db update. Then it deletes the flag file(s), goes to sleep again and tells Windows to wake it up when another file appears in the folder.
This is working exactly as you described: the triggered update runs shortly after the main database event, and voila, the user doesn't have to wait the extra few seconds. I just love it.
You don't necessarily need to compile your own executable to do this: many scripting languges can use Windows Change Notification. I wrote the background process in Perl and it only took a few minutes to get it working.