Post reply

CLR Triggers in SQL Servers

  • June 6, 2009 at 11:37 am

    #132732

    Comments posted to this topic are about the item CLR Triggers in SQL Servers

    Best wishes,
    Phil Factor

  • June 6, 2009 at 11:28 pm

    #1005774

    we use them for debugging mostly.

  • June 8, 2009 at 7:47 am

    #1006069

    I would agree that keeping the trigger as light as possible is in the best interest of the database. We do not use CLR triggers and use T-SQL triggers sparingly (for change history mostly). I could see the case where a web service call could be made to an independent system and a trigger would allow this call to participate in a transaction on the original record. As an example, an order entry is inserted into SQL and a web service call to a fulfillment system says "unavailable" in which case the SQL Server could cancel the insert. Probably not the best design but one that would need the CLR.

  • June 8, 2009 at 9:47 am

    #1006134

    All of it seems really strange to me. Views don't rate on this, not even 2 of them? What about user defined functions? And as you point out, the correlation (or lack) between table count and primary key constraint is something to raise eyebrows about. I'm not a formal DBA and my databases are all less than 1 gig and still all my tables manage to have a primary key and a clustered index. I don't use a lot of views, but how could anyone not use even 2 user defined functions on a database big enough to have 10 gigs of data? (Or is that grouped with stored procedures?)

    I don't know what a "feature usage report" is or how they get their data, but it all looks rather skewed to me. Maybe because it is for SQL 2008 and the early adopters are not "normal" users? I'm not making that claim. I'm just trying to make sense of the numbers. So strange.

  • June 8, 2009 at 11:36 am

    #1006202

    Steve: Yes, me too, but I would never be allowed to put these in a production system, and I'd use the more convenient TSQL triggers unless, of course, I used the trick that allows a generic CLR trigger to be used. Hmm. If they were using Generic CLR triggers for debug or audit, they'd only need one per database!

    Rob: That is a very good point. It could be that someone would want to do this, though I'd have imagined it would be safer to use Service Broker for this sort of job, in order to keep it asynchronous.

    JJ B: Ooh. I wonder if you're right. I certainly share your doubts about the data, but it still seems odd to me that the early adopters (or whatever they are) should be so enamored of CLR Triggers. It worries me that I'm missing out on a particular advantage of having them.

    Best wishes,
    Phil Factor

  • June 8, 2009 at 2:36 pm

    #1006308

    ...enamored of CLR Triggers. It worries me that I'm missing out on a particular advantage of having them.

    I would want to know what they are being used for too. I think it is a very good question (if the data is legit). I'm glad you asked.

    Which brings me right back to: The lack of response to your editorial on this forum is telling. If there is some great big and wonderful use for CLR triggers, I would have thought you would have received some great replies on this forum. SQLServerCentral has lots of active forum members and heavy SQL Server users. Yet you get barely a handful of replies with no great new info. Just saying...

    Thanks for bringing it up anyway. Your article gave me lots to think about.

  • June 9, 2009 at 2:29 pm

    #1007059

    I can just see it now: After detailed studies, it turns out that the CLR triggers all come from one dev database on one server, being used for proof-of-concept testing, and the "track feature use" option was turned on for that server. Of course, in that same server, they have a ton of tables without PKs, because those are the two concepts they've been testing for the last few months.

    Outside of silliness like that, no, I can't say anything useful on this one. I'm not sure why you'd have all those CLR triggers, unless they are part of some complex data validation system that involves web services or some such.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 7 posts - 1 through 6 (of 6 total)

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