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.