THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Using Column Names with IsUpdatedColumn() in SQL CLR Triggers

As a preface to actually writing the meat of what this blog post is about, I have to admit that I am not a fan of using triggers in general, and I am even less of a fan of having SQL CLR Triggers in all but the most obscure of scenario, but that is just my own personal opinion on things, and not necessarily in line with what others think or consider to be good design. 

While cruising the forums I came across a post that wanted to know how to determine if a column was updated using the SqlTriggerContext.IsUpdatedColumn() method which only accepts an integer parameter for the column ordinal to check, and returns a boolean value for whether the column changed or not.  The poster's question was how to do this using the column name instead of hard coding a ordinal position so that the table schema can be changed if need be without affecting the trigger.

If you are unfamiliar with SQL CLR triggers, then you will probably want to gather some background on the subject in the following BOL Topics:

How to: Create and Run a CLR SQL Server Trigger

Introduction to SQL Server CLR Integration

Walkthrough: Debugging a SQL CLR Trigger

Fundamentally, what was being asked is actually a simple thing to do, it just may not seem that way.  There are probably more than one method out there to do this, but a simple method that worked in my initial testing is:

[Microsoft.SqlServer.Server.SqlTrigger (Name="Trigger1", Target="Table1", Event="FOR UPDATE")]
public static void Trigger1()
{
    SqlConnection conn
= new SqlConnection("context connection=true;");
   
SqlCommand cmd = new SqlCommand("select top 0 * from Table1", conn);

   
conn.Open();

   
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);

   
if (SqlContext.TriggerContext.IsUpdatedColumn(dr.GetOrdinal("columnname")))
   
{
        SqlContext.Pipe.Send
("columnname was updated");
   
}
   
else
   
{
        SqlContext.Pipe.Send
("columnname was not updated");
   
}

    dr.Close
();
   
conn.Close();
}


There really is no magic or trick here, I am just using a SqlDataReader to get the table schema, and then invoking the GetOrdinal() method for the column name to find the position of the column to pass to IsUpdatedColumn().  This way, you can are not hard coding the ordinal position of a column into your code.

Published Wednesday, May 06, 2009 4:11 AM by Jonathan Kehayias

Comments

 

Adam Machanic said:

I think the story with CLR triggers could be a LOT better.  There are a number of little features that could be provided that would make the development experience much easier and more worthwhile.  I've listed several ideas in the Connect item below; please vote if you agree.

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=265346

May 6, 2009 8:25 AM
 

Denis Gobo said:

Does IsUpdatedColumn fire if a column gets updated bu the value doesn't actually changes

something like

UPDATE Table

Set value = 100

WHERE value = 100

In a T-SQL trigger IF UPDATE(Value) will return true even though the value didn't change. You then need to compare deleted and inserted pseudo table values and also account for NULLs

May 7, 2009 5:02 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement