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.