Determine changed column(s) by name instead of ordinal

Question

Determine changed column(s) by name instead of ordinal

mrlucmorin

Old Hand

Points: 306
More actions
May 3, 2009 at 2:43 pm

#208953

Hi all,
in a CLR Trigger, it is possible to determine if a column was actually updated by using the SqlTriggerContext.IsUpdatedColumn() method. This methods receives an integer parameter, which is the column ordinal, and returns true if the column was updated.
I find this quite inconvenient as the Table definition might change over time, and relying on column ordinal is, in my opinion, asking for long term problems.
Is there a way to achieve the same but using the column name ?
Thank you.
Luc Morin
Luc Morin, T.P.
http://www.stlm.ca

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

What is your trigger doing that makes it require CLR over standard TSQL? Triggers in TSQL can be problemattic enough for performance and troubleshooting problems, and often are not necessary with some simple design or process changes.

mrlucmorin Old Hand Points: 306 More actions · Answer 2

Hi,

Thanks for taking time to reply to my post.

I must say I'm a bit surprised to see someone posting on a CLR Intergration forum and trying to discourage people from using it :w00t:

As for the relevance of using TSQL or CLR, I understand your concerns. Though what I'm doing is not impossible in TSQL, the use of C# + Linq syntax makes my trigger much more concise.

In most simple cases, using TSQL makes sense, but I personally think that it's not a "real" programming language and it keeps me from getting the job done when more complex tasks are needed. I much prefer the syntax, and have yet to experience any side effects from using CLR/C#.

Now, about my original question ? 😛

Regards.

Luc Morin, T.P.
http://www.stlm.ca

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

mrlucmorin (5/5/2009)
I must say I'm a bit surprised to see someone posting on a CLR Intergration forum and trying to discourage people from using it :w00t:

You obviously haven't met Jeff Moden on the forums yet. There are quite a few of us who champion SQLCLR in specific areas, and recommend against it in most of the places where we see it being used. I'd posit that 80% of the time that I find SQLCLR being used, TSQL is actually more appropriate, and better performing.

As for the relevance of using TSQL or CLR, I understand your concerns. Though what I'm doing is not impossible in TSQL, the use of C# + Linq syntax makes my trigger much more concise.
In most simple cases, using TSQL makes sense, but I personally think that it's not a "real" programming language and it keeps me from getting the job done when more complex tasks are needed. I much prefer the syntax, and have yet to experience any side effects from using CLR/C#.

I could put a bit of effort into arguing this, but it generally is pointless to do so, and it is your database, not mine, so it really doesn't affect me if you overuse/misuse SQLCLR in your own designs. Just be aware that Microsoft's own recommendations are to use TSQL anywhere that the procedural code has optimized methods of handling processing. Your use of CLR should be very limited, and TSQL should predominately be used in your database design. Your use of triggers in database design should be even more limited than potential use for CLR.

I assume that since you mention LINQ that you are using SQL Server 2008, or you have accepted loading UNSAFE Assemblies into your environment because LINQ is not supported in SQL Server 2005 by Microsoft.

As for your particular question, there is no overload for the IsUpdatedColumn() method that will accept a string. To get around this, open a SqlDataReader for a SqlCommand that performs a select against the table with the CommandBehavior.SchemaOnly option set. Then use the GetOrdinal() method of the datareader to find the current ordinal of the column by column name and use this internally in the IsUpdatedColumn() method as follows:

SqlDataReader dr = new SqlCommand("select top 0 * from Table1",

new SqlConnection("context connection=true;"))

.ExecuteReader(CommandBehavior.SchemaOnly);

if (SqlContext.TriggerContext.IsUpdatedColumn(dr.GetOrdinal("columnname")))

{

//put your code here

}

I would hope that you are running SQL on a 64bit server if you are heavily using SQLCLR, and that you have your Max Server Memory settings properly adjusted to leave available memory for your SQL CLR assemblies to load and operate in. Otherwise you will experience frequent appdomain unloads due to memory pressure.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

CORRECTION:

In trying to simplify the code down, I left out some stuff, but you should have been able to work it out, but for completeness sake, here is the correct code:

SqlCommand cmd = new SqlCommand("select top 0 * from Table1", new SqlConnection("context connection=true;"));

cmd.Connection.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();

cmd.Connection.Close();

mrlucmorin Old Hand Points: 306 More actions · Answer 5

Jonathan,

Yes, thank you for the code snippet.

In view of your comments about CLR Integration, I'll rethink my use of it. In the application that we're developing, we don't use many triggers, and so far, mine is the only CLR one.

I guess my being comfortable with Linq and C# makes me cringe at using TSQL for anything more than a few lines of code :crazy:

But as you mention, if there's any risk of performance issues, I'd like to investigate some more before deploying.

Thanks for the heads up.

Regards.

Luc Morin, T.P.
http://www.stlm.ca

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

If you can post your specific problem in detail here, I would bet that we can probably offer you some suggestions as to the best fit solution to your problem.

mrlucmorin Old Hand Points: 306 More actions · Answer 7

Jonathan,

The use case is quite simple.

When a row is inserted/updated/deleted to the OrderDetails table, if any of the row refers to an Item that is defined as a Kit, we want to insert/update/delete the Kit SubItems in the OrderDetails table as well.

For example, on insertion of a Kit composed of 5 SubItems, we will thus end up with 6 lines in OrderDetails: One for the 'Parent Item', and then one for each 'SubItem'.

My problem comes from the fact that I'm much more used to think in terms of procedural programming than to think in terms of result sets. I often find myself using a CURSOR, and then someone shows me a way to do it only with a result set.

So for me, going CLR is the lazy way.

I'm currently rewriting the INSERT trigger, and I found that I was able to use a result set just fine. I think the DELETE trigger will be easy too. My concern is the UPDATE trigger.

Most of the time, I write TSQL triggers that will perform perfectly if only one row is inserted/updated/deleted, but will not work if many rows are involved.

Bottom line is: I suck at TSQL :blush:

Regards

Luc Morin, T.P.
http://www.stlm.ca

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

mrlucmorin (5/5/2009)
Jonathan,
The use case is quite simple.
When a row is inserted/updated/deleted to the OrderDetails table, if any of the row refers to an Item that is defined as a Kit, we want to insert/update/delete the Kit SubItems in the OrderDetails table as well.
For example, on insertion of a Kit composed of 5 SubItems, we will thus end up with 6 lines in OrderDetails: One for the 'Parent Item', and then one for each 'SubItem'.
My problem comes from the fact that I'm much more used to think in terms of procedural programming than to think in terms of result sets. I often find myself using a CURSOR, and then someone shows me a way to do it only with a result set.
So for me, going CLR is the lazy way.
I'm currently rewriting the INSERT trigger, and I found that I was able to use a result set just fine. I think the DELETE trigger will be easy too. My concern is the UPDATE trigger.
Most of the time, I write TSQL triggers that will perform perfectly if only one row is inserted/updated/deleted, but will not work if many rows are involved.

This is actually a common problem people have with Triggers and is one of the biggest factors for poor trigger performance that I see. Keep in mind that inside the trigger, you can join to the inserted and deleted tables, and you can join them to each other as well, so you can generally create a set based solution that fits if you look at the problem from the correct angle. The fact that you can get the Inserted and Deleted triggers to work set based is a big step forward already. What are the specific use cases for updates to a Kit for an Order? That is the fundamental start point for the problem. If you can post the code from the Insert trigger that you have, we can look to offer ideas for the Update trigger.

Bottom line is: I suck at TSQL :blush:
Regards

We all started somewhere. By the same note, you could say I suck at CLR programming. I am pretty good at it, but no where near competitive to a lot of people out there, and while my code works, and I have a good understanding of design patterns and practices, I probably write extremely inefficient code memory wise in places.

I used to write everything in TSQL because that is all that I knew, but the one thing I have learned over the last 4 years is that just because you can, doesn't mean you should. I have written TSQL code that does everything from generating XML Excel documents to disk that get emailed as attachments, to a full billing system in TSQL. Neither of these tasks belong in TSQL and both work much faster when written properly in .NET.

It is normal to fall back onto what is comfortable when writing code. The good thing here is that you are willing to learn the other side of things which makes you much more proficient in the long run. If you can post what you have, we'll take a stab at that UPDATE trigger in tSQL and help you out.

mrlucmorin Old Hand Points: 306 More actions · Answer 9

Jonathan,

Sorry, I was busy with some other aspect of our project. Had to put the trigger aside for a while.

I've done some googling, and it would seem that a common technique to UPDATE multiple rows within a trigger (and to do it using a set based approach) is to use correlated queries.

In MS SQL, from what I understood, this can also be achieved with the UPDATE...FROM syntax.

Am I on the right track with this ?

Sometimes the biggest hurdle is not knowing what to look for, or that what you're looking for actually has a name 😀

Regards.

Luc Morin, T.P.
http://www.stlm.ca

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 10

Hi

I'm also a C# developer.

As first your initial question. If you still trying to use your CLR trigger I would suggest to request the column names again and again whenever the trigger becomes fired. You can use a "static readonly Dictionary" to get them only once but you have to handle the multi-threading.

Here a little sample (not tested but should work):

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public partial class Triggers

{

private static readonly Dictionary _columnNames = new Dictionary();

private const string MY_TABLE_NAME = "MyTable";

// Enter existing table or view for the target and uncomment the attribute line

// [Microsoft.SqlServer.Server.SqlTrigger (Name="Trigger1", Target="Table1", Event="FOR UPDATE")]

public static void Trigger1()

{

string[] columns;

if (!_columnNames.TryGetValue(MY_TABLE_NAME, out columns))

{

#region Get column names from database

// Lock the dictionary

lock (_columnNames)

{

// Check again to avoid other threads have already done

if (!_columnNames.TryGetValue(MY_TABLE_NAME, out columns))

{

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

using (SqlCommand cmd = new SqlCommand(string.Format("SELECT TOP(0) * FROM {0}", MY_TABLE_NAME), cn))

{

using (SqlDataReader reader = cmd.ExecuteReader())

{

// Get the field names

int fieldCount = reader.FieldCount;

columns = new string[fieldCount];

for (int i = 0; i < fieldCount; i++)

{

columns = reader.GetName(i);

}

_columnNames.Add(MY_TABLE_NAME, columns);

}

#endregion

}

////////////////////////////////////////

// Add your real trigger code here

////////////////////////////////////////

// Replace with your own code

SqlContext.Pipe.Send("Trigger FIRED");

}

However, I confirm Johnathan. Things like usual DML should be done in SQL; not in CLR. SQL is much faster for these things. You are correct a usual "UPDATE FROM"/"INSERT INTO"/"DELETE FROM" is the way to go 😉

Greets

Flo