Serial numbers created and modified in SQL Server

Question

I need to add serial numbers for most of the objects in my application, because I will use the Lucene search index side by side.

Instead of starting the current polling process or manually starting my indexer on my application, I think of the following:

• Add a Created column with the default value of GETUTCDATE() .
• Add a Modified column with the default value of GETUTCDATE() .
• Add an ON UPDATE trigger to the table that updates Modified to GETUTCDATE() (can this happen as UPDATE is executed, that is, it adds SET [Modified] = GETUTCDATE() to the SQL query instead of updating it separately after?)
• The ON UPDATE trigger will call my Lucene index index to update its index (it should be like calling xp_cmdshell presumably, but is there a way to send a message to the process, and not start a new one? I heard that I can use Named Pipes, but how do you use named pipes from Sproc or trigger? (searching for "SQL Server named pipe" gives me irrelevant results, of course).

Does this sound normal, and how can I solve small problems?

Answer 1

As I understand it, you need to enter two columns into existing tables and process them (in the east of one of them) in "runtime" and use an external component.

Your first three moments are nothing unusual. There are two types of triggers in SQL Server depending on the processing time of the trigger: the INSTEAD OF trigger (actually processed before insertion) and the AFTER trigger. However, inside the INSTEAD OF trigger, you must provide the logic to really insert data into the table, as well as other processing you need. I usually avoid this unless it is really needed.

Now about your fourth point - it's complicated, and there are several approaches to solving this problem in SQL Server, but all of them are at least a little ugly. Basically, you need to either execute an external process or send a message to it. I really have no experience with the Lucene index, but I assume that one of these methods (execute or send a message) will be applied.

So, you can do one of the following to directly or indirectly access an external component, which means direct access to the Lucene indexer directly or through a proxy module:

• Implement unsafe CLR startup ; basically you execute the .NET code inside the trigger and, therefore, gain access to the whole (be careful with this - not exactly like that) .NET framework
• Implement an unsafe CLR procedure ; the only difference with the CLR trigger is that you won’t call it imediately after the INSERT, but you can handle the database task that runs periodically
• Use xp_cmdshell ; You already know about this, but you can combine this aproach with the technique of wrapping at the last point.
• Web service call this method is usually referred to as experimental. And you must implement this service yourself (if the Lucene index does not install any web service yourself)
• Of course, there are other methods that I can’t think about right now ...

I would personally go with the third point (job + xp_cmdshell) because of the simplicity, but this is simply because I lack knowledge of how the Lucene indexer works.

EDIT (another option):

Use query notifications ; SQL Server Service Broker allows an external application to connect and track interesting changes. You even have several options for how to do this (mostly synchronous or asynchronous), only the precondition is that your service Borker is working, working and available for your application. This is a more complex method of informing the external component that something has changed.