Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CLR Triggers on SQL 2008 R2 // Disable vs. Drop Expand / Collapse
Author
Message
Posted Thursday, August 7, 2014 3:24 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 1,669, Visits: 2,220
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)

Internet ATM Machine
Post #1600912
Posted Friday, August 8, 2014 8:37 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 1,669, Visits: 2,220
Gail Shaw? Jeff Moden? Anyone?

Steve
(aka sgmunson)

Internet ATM Machine
Post #1601212
Posted Friday, August 8, 2014 9:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1601220
Posted Friday, August 8, 2014 9:34 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 1,669, Visits: 2,220
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)

Internet ATM Machine
Post #1601237
Posted Friday, August 8, 2014 10:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1601255
Posted Friday, August 8, 2014 10:31 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 1,669, Visits: 2,220
Um... that's not actually an option. Don't get me started on the why...

Steve
(aka sgmunson)

Internet ATM Machine
Post #1601267
Posted Friday, August 8, 2014 3:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1601344
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse