在SQL Server中创建和修改的序列号 [英] Serial numbers, created and modified in SQL Server

问题描述

我需要在我的应用程序中的大多数实体中添加序列号，因为我将并排运行Lucene搜索索引.

I'm needing to add serial numbers to most of the entities in my application because I'm going to be running a Lucene search index side-by-side.

不必考虑正在进行的轮询过程，也不必通过应用程序手动运行索引器，而是在考虑以下问题:

Rather than having to run an ongoing polling process, or manually run my indexer by my application I'm thinking of the following:

• 添加默认值为GETUTCDATE()的Created列.
• 添加默认值为GETUTCDATE()的Modified列.
• 将ON UPDATE触发器添加到将Modified更新为GETUTCDATE()的表中(在执行UPDATE时会在 发生这种情况吗?即它将SET [Modified] = GETUTCDATE()添加到SQL查询而不是之后再单独更新?)
• ON UPDATE触发器将调用我的Lucene索引器以更新其索引(大概必须是xp_cmdshell调用，但是有没有一种方法可以向进程发送消息而不是开始新的消息?听说我可以使用命名管道，但是如何在Sproc或触发器中使用命名管道呢(搜索"SQL Server命名管道"当然会得到无关紧要的结果).

• Add a Created column with a default value of GETUTCDATE().
• Add a Modified column with a default value of GETUTCDATE().
• Add an ON UPDATE trigger to the table that updates Modified to GETUTCDATE() (can this happen as the UPDATE is executed? i.e. it adds SET [Modified] = GETUTCDATE() to the SQL query instead of updating it individually afterwards?)
• The ON UPDATE trigger will call my Lucene indexer to update its index (this would have to be an xp_cmdshell call presumably, but is there a way of sending a message to the process instead of starting a new one? I heard I could use Named Pipes, but how do you use named pipes from within a Sproc or trigger? (searching for "SQL Server named pipes" gives me irrelevant results, of course).

听起来不错，如何解决小子问题?

Does this sound okay, and how can I solve the small sub-problems?

推荐答案

据我了解，您必须在现有表中引入两列，并在运行时"中对其进行处理(在其中的一列中)，并由外部组件.

As I understood, you have to introduce two columns to your existing tables and have them processed (at east one of them) in 'runtime' and used by an external component.

您的前三点并不稀奇.根据处理触发器的时间，SQL Server中有两种类型的触发器:INSTEAD OF触发器(在插入发生之前实际已处理)和AFTER触发器.但是，在INSTEAD OF触发器内部，您必须提供逻辑以真正将数据插入表中，以及所需的其他自定义处理.我通常会避免不必要的情况.

Your first three points are nothing unusual. There are two types of triggers in SQL Server according to time when trigger get processed: INSTEAD OF trigger (actually processed before insert happens) and AFTER trigger. However, inside INSTEAD OF trigger you have to provide logic what to really insert data into the table, along with other custom processing you require. I usually avoid that if not really necessary.

现在，关于您的第四点-这很棘手，在SQL Server中有几种方法可以解决此问题，但是所有这些方法至少都有些丑陋.基本上，您必须执行外部过程或向其发送消息.我真的没有任何关于Lucene indexer的经验，但是我想其中一种方法(执行或发送消息)会适用.

Now about your fourth point - it's tricky and there are several approaches to solve this in SQL Server, but all of them are at least a bit ugly. Basically you have to either execute external process or send message to it. I really don't have any experience with Lucene indexer but I guess one of these methods (execute or send message) would apply.

因此，您可以执行以下操作之一以直接或间接访问外部组件，这意味着直接或通过某些代理模块访问Lucene索引器:

So, you can do one of the the following to directly or indirectly access external component, meaning to access Lucene indexer directly or via some proxy module:

1. 实施不安全的CLR触发器；基本上，您在触发器内执行.NET代码，从而可以访问整个.NET框架(请注意-并非完全如此)
2. 实施不安全的CLR程序；与CLR触发器的唯一区别是，您不会在INSERT之后立即调用它，但是您可以使用一些周期性运行的数据库作业来做得很好
3. 使用xp_cmdshell ；您已经知道了这一点，但是您可以在最后一点将这一方法与工作包装技术结合起来
4. 致电网络服务；此技术通常标记为实验性的，并且您必须自己实现服务(如果Lucene indexer不能自行安装某些Web服务)
5. 肯定还有其他我现在无法想到的方法...

1. Implement unsafe CLR trigger; basically you execute .NET code inside the trigger and thus get access to the whole (be careful with that - not entirely true) .NET framework
2. Implement unsafe CLR procedure; only difference to CLR trigger is that you wouldn't call it imediatelly after INSERT, but you will do fine with some database job that runs periodically
3. Use xp_cmdshell; you already know about this one, but you can combine this aproach with job-wrapping technique in last point
4. Call web service; this technique is usually marked as experimental AND you have to implement the service by yourself (if Lucene indexer doesn't install some web service on its own)
5. There surely are other methods I can't think of right now...

由于简单，我个人会选择第三点(job + xp_cmdshell)，但这只是因为我对Lucene索引器的工作原理一无所知.

I would personally go with third point (job+xp_cmdshell) because of the simplicity, but that's just because I lack any knowledge of how does the Lucene indexer work.

编辑(另一个选项):

使用查询通知； SQL Server Service Broker允许外部应用程序连接和监视有趣的更改.您甚至可以选择几种方法(基本上是同步或异步)来实现此目的，唯一的前提是服务Borker已启动，正在运行且可用于您的应用程序.这是一种更复杂的方法，用于通知外部组件某些更改.

Use Query Notifications; SQL Server Service Broker allows an external application to connect and monitor interesting changes. You even have several options how to do that (basically synchronous or asynchronous), only precondition is that your Service Borker is up, running and available to your application. This is more sophisticated method to inform external component that something has changed.

这篇关于在SQL Server中创建和修改的序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！