How To Forward Data To An External System If There Were Changes During An INSERT or UPDATE? Use a Trigger? Use Extended Sproc or CLR Integration?

Question

Here's the basics of the system we have in place today. We have a SQL 2005/2008 table defined as:

CREATE TABLE [dbo].[Profiles] (
    [Firm] [char] (4) NULL ,
    [Account] [char] (10) NOT NULL ,
    [UndSym] [char] (24) NOT NULL ,
    [Updated] [timestamp] NOT NULL ,
    [Data] [image] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The Data field is a BLOB. It is where virtually all the goodies are. The data in the BLOB is compressed text which looks very similar to an INI file, with key-value pairs within sections. The internal layout of this INI file is quite complex however with much inter-related data.

We also have another product family which is completely different in design, implementation, and target audience. But now we have a client coming online who wants to integrate the two systems at the database level.

My system is MSSQL. The other system is MySQL. Between the two sits a custom middleware solution of our own design. I don't want to connect to MySQL directly, and they don't want to connect directly to me, for various reasons which are sound and are beyond the scope of this conversation, so don't worry about that.

The concept is simple. When a row in my Profiles table is touched, I need to rip thru the BLOB and look for a few bits of data. If those bits of data has changed, I need to send just those bits of data (not the whole BLOB) to the middleware piece. I must only do this when there are changes I'm interested in, and the only way I can do that is to process both blobs (the original and the replacement).

I quickly concluded that using T-SQL to process the BLOB is not the road I want to go down. I need to write custom code, and I am constrained to using either unmanaged C++, managed C++ or C# to do it.

There are a couple questions here.

FIRST: Should I use an INSTEAD OF trigger to, well, trigger the code which will compare the BLOBs and send interesting data? I know there are some extremist schools of thought on triggers: some people think they are pure Evil, others think they are awesome. The truth is somewhere in between. If I don't use a trigger, how can I trigger processing the BLOBs?

SECOND: Should I use:

1. An Extended Stored Procedure, written in unmanaged C++?
2. A CLR Stored Procedure written in either Managed C++ or C#?
3. Something else?

...to do the actual processing of the BLOBs and sending the data to the middleware?

Answer 1

Don't connect to an external process in a trigger, you'll create yourself a mess.

Add a trigger on the table that on insert/update uses SEND to enqueue a message to a local service. Attach an activated procedure to the service queue. After the INSERT/UPDATE commits the enqueued message will activate the procedure, you can dequeue the message and connect to your middleware logic (eg. via CLR). This way you decouple the update/insert transaction from the notification. This is beneficial for both performance (no longer have to wait for the connect to middleware to complete during the trigger) and correctness (the INSERT/UPDATE can safely rollback w/o having to enroll your middleware and/or MySQL in a distributed transaction).

You can have your 'interesting data' detection logic either in the trigger or in the activated procedure. If is T-SQL I'd put it in the trigger. If you place it in CLR procedure, I'd put it in the activated part.

For your activated procedure to be able to connect externally you'll most likely need to code sign the part that connects to your middleware. See here an example of how to sign an activated procedure.

Answer 2

Agree with Remus, don't connect your trigger to an external process. If you can wait a few minutes to synch up the data, consider sending all the blobs of interest to a separate table through the trigger. Then have a job that runs every five minutes or so and picks up processes the data and sends to the other system, then marks those records as processsed. This also has the advantage of being able to look back in time and see when particular data was sent to the other system. Be sure to consider race conditions here, make sure you pull all the data to be sent to a temp table or table varaible and process from htere so that you don't get records added to the table during your process that are not sent but get marked as sent.

If you can guanantee the data will never be changed outside the user interface, you can consider adding the procssing step to the code for the insert or update process instead of using a trigger. This is dangerous though when trying to keep two systems in synch.