1

I just implemented a prototype solution for updating my caching server in real-time by assigning a CLR Trigger to a table so that whenever a certain column is updated the URL called from the trigger will update the caching server with the correct data.

It's working fine and the code is as follows:

[Microsoft.SqlServer.Server.SqlTrigger(Name = "AdStatusChanged", Target = "Ads", Event = "FOR UPDATE")]
public static void AdStatusChanged()
{
    SqlTriggerContext triggContext = SqlContext.TriggerContext;
    int adID = 0, adStatusID_Old = 0, adStatusID_New = 0;

if (triggContext.TriggerAction == TriggerAction.Update)
{
    using (SqlConnection conn = new SqlConnection("context connection=true"))
    {
        conn.Open();
        SqlCommand sqlComm = new SqlCommand();
        SqlPipe sqlP = SqlContext.Pipe;

        sqlComm.Connection = conn;
        sqlComm.CommandText = "SELECT AdID, AdStatusID from INSERTED";

        SqlDataReader reader = sqlComm.ExecuteReader();

        if (reader.Read())
        {
            adID = reader.GetInt32(0);
            adStatusID_New = reader.GetInt32(1);
        }

        reader.Close();

        sqlComm.CommandText = "SELECT AdID, AdStatusID from DELETED WHERE AdID = " + adID;

        reader = sqlComm.ExecuteReader();

        if (reader.Read())
        {
            adID = reader.GetInt32(0);
            adStatusID_Old = reader.GetInt32(1);
        }
    }

    if (adID == 0 || adStatusID_New == adStatusID_Old)
    {
        // Check could be more thorough !
        return;
    }

    WebResponse httpResponse = null;

    try
    {
        string apiURL = string.Format("{0}/{1}", "http://localhost:14003/Home", "UpdateAdStatus?adID=" + adID + "&adStatusID=" + adStatusID_New);

        var httpWebRequest = (HttpWebRequest)WebRequest.Create(apiURL);
        httpWebRequest.Method = "GET";

        httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();

        // check for successful response
    }
    catch (Exception ex)
    {
        Log("WebRequest from within SQL Server failed ! " + ex.Message);
    }
    finally
    {
        if (httpResponse != null)
        {
            httpResponse.Close();
        }
    }
}

}

I would like to have some expert/experienced views on the "CONS" of this approach regarding performance, deadlocks, sql crashing, or other areas that could be of potential concern.

Has anyone tried this (I'm sure many must have) and what was the result ? a successful implementation or did you revert to some other method or updating the cache real-time?

Storm
  • 4,307
  • 11
  • 40
  • 57
  • Typically folks use much less elaborate mechanisms for this, such as [query notifications](http://msdn.microsoft.com/en-us/library/ms130764.aspx) or simply exposing the modified date of each row and having the service poll to see if there are newly updated rows. The data access you are doing within the CLR trigger to determine if the old and new values are different seems to be inefficient as well on first glance. – Aaron Bertrand Jul 09 '12 at 12:54
  • Thx @AaronBertrand. I would appreciate if you could elaborate a little on how the data access could be made more efficient, if I plan to pursue this path. – Storm Jul 10 '12 at 07:36
  • I also read up on Query Notification, but it says that I can only subscribe for a certain "query" and will get notified when its result set changes. So that means, if I have a million active ads in my database, my caching server must have a million subscriptions to the db server to be notified anytime an "adstatus" changes !! That sounds pretty insane :( – Storm Jul 10 '12 at 08:51
  • I don't think a million subscriptions is what I was suggesting. The general problem I have is that a trigger firing and hitting a URL for any number of up to a million ads changing is not ideal either. Does the caching server really need to know about every single ad change immediately? Or can it be notified of the *sum* of changes every five or n minutes? – Aaron Bertrand Jul 10 '12 at 13:30
  • Got your point :) Thx. I was under the impression that the trigger will be called separately for each row changed. I can change my implementation to do batch updates to the caching server. Comments ? – Storm Jul 12 '12 at 07:56
  • I'm not sure which parts you want comments on? A trigger is called per statement, not per row, so if you update 1000 rows your current implementation (and [the examples documented here](http://msdn.microsoft.com/en-us/library/ms254959(v=vs.80).aspx)) will only deal with one arbitrary row instead of all 1000 (your reader would need a loop in order to deal with all rows in a multi-row update). I would say as part of your update, update a column with an updated timestamp, then have a scheduled background process poll for changed ads and notify the caching server. – Aaron Bertrand Jul 12 '12 at 12:31

0 Answers0