SQL Clone
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
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7048 Visits: 4376
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)
Smile Smile Smile
Health & Nutrition
sgmunson
sgmunson
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7048 Visits: 4376
Gail Shaw? Jeff Moden? Anyone?

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86824 Visits: 41103
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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

Group: General Forum Members
Points: 7048 Visits: 4376
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)
Smile Smile Smile
Health & Nutrition
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86824 Visits: 41103
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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

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

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86824 Visits: 41103
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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