August 7, 2014 at 3:24 pm
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 8, 2014 at 8:37 am
Gail Shaw? Jeff Moden? Anyone?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 8, 2014 at 9:01 am
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
Change is inevitable... Change for the better is not.
August 8, 2014 at 9:34 am
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 8, 2014 at 10:08 am
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
Change is inevitable... Change for the better is not.
August 8, 2014 at 10:31 am
Um... that's not actually an option. Don't get me started on the why...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 8, 2014 at 3:40 pm
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
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply