Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CLR Triggers on SQL 2008 R2 // Disable vs. Drop


CLR Triggers on SQL 2008 R2 // Disable vs. Drop

Author
Message
sgmunson
sgmunson
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2837 Visits: 3757
I'm running into repeat occurrences of a trigger no longer functioning, and in the most recent case, informed by the DBA that the trigger "wasn't there". This is a CLR trigger, and what I need to know is what actually happens to the assembly when a trigger is dropped. If the assembly "goes away", then re-establishing the trigger with CREATE TRIGGER would have to reference a file path somewhere, wouldn't it? Just using the same assembly name as before wouldn't work, would it? I searched the forum for trigger stuff, but couldn't find anything about exactly how this works. I don't know for sure if that "going away" thing is the actual cause, or someone's just not owning up to a disable, but I need to be authoritative on what happens in order to effectively inform the decision makers. Can anyone illuminate this process and how things work ?

Steve
(aka sgmunson)
:-):-):-)
Health & Nutrition
sgmunson
sgmunson
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2837 Visits: 3757
Gail Shaw? Jeff Moden? Anyone?

Steve
(aka sgmunson)
:-):-):-)
Health & Nutrition
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44994 Visits: 39880
sgmunson (8/8/2014)
Gail Shaw? Jeff Moden? Anyone?


To the best of my knowledge, dropping a trigger doesn't drop the underlying DDL but I'm no CLR guru, either. Even if it did, once you have it generated, you can script it out so that it actually contains the binary and a proc could be used to easily re-establish it. Of course, that's just putting a band-aid on a stab wound...

I'm sure that I'm preaching to the choir but the big thing to do would be to find out what's happening to the trigger to begin with. I don't remember if trigger drops (specifically) are logged in the "Default Trace", but that's the first place that I'd start looking.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sgmunson
sgmunson
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2837 Visits: 3757
Jeff,

I'm not sure we're dealing with some kind of SQL script - either for when they copy production down to a lesser environment (DEV, TEST, etc.), or for a conversion into that lesser environment, or, if it's when they RESTORE into the lesser environment. I was hoping to at least understand what exactly happens to the assembly if you DROP the trigger as opposed to merely disabling it. It can at least make sense if the assembly "goes away" in a DROP, but not with a DISABLE, because it would need to still exist in order for the ENABLE to work. As I don't have and can't get access to the default trace, I have no means to look there. Do you know what system database handles the CLR assemblies?

Maybe Gail Shaw (aka GilaMonster) can chime in? Or maybe Grant Fritchey, or Lynn Pettis?

Steve
(aka sgmunson)
:-):-):-)
Health & Nutrition
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44994 Visits: 39880
sgmunson (8/8/2014)
As I don't have and can't get access to the default trace, I have no means to look there. Do you know what system database handles the CLR assemblies?


Heh... hold the phone, ol' firend... you DO HAVE a DBA. Get the DBA involved. It IS a part of the job of a DBA to assist in problems like this. A little should-to-shoulder time with the DBA driving would go a long way here.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sgmunson
sgmunson
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2837 Visits: 3757
Um... that's not actually an option. Don't get me started on the why...

Steve
(aka sgmunson)
:-):-):-)
Health & Nutrition
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44994 Visits: 39880
sgmunson (8/8/2014)
Um... that's not actually an option. Don't get me started on the why...


Heh... been there, done that and totally understood. No explanation needed.

Assemblies are stored in the database and are backed up and restored just like anything else. Beyond that, I'd have to hit BOL and/or "Yabingooglehoo" for other questions/answers.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search