How did my SQL Server 2008 trigger get deleted?

This is a very strange problem. I've asked about it before here: How did my trigger get deleted?

I renamed my trigger and I thought it had stopped happening but the problem seems to have come back again.

I've added a trigger to a table in our database. The server is SQL 2008. The 开发者_如何学Gotrigger doesn't do anything particularly tricky. Just changes a LastUpdated field in the table when certain fields are changed. It's a "After Update" trigger.

There is a large C++ legacy app that runs all kind of huge queries against this database. Somehow (I've got absolutely no idea how) it is deleting this trigger. It doesn't delete any other triggers and I'm certain that it's not explicitly dropping the trigger or table. The developers of this app don't even know anything about my triggers.

How is this possible?

I've tried running a trace using SQL Server Profiler and I've gone through each command that it's sending and run them using SQL Management Studio but my trigger is not affected. It only seems to happen when I run the app.

The other devs reckon that they are not deleting it explicitly. It doesn't exist in sys.objects or sys.triggers so it's not a glitch with SSMS. Guess I'll just rename it and hope for the best? I can't think of anything else to try. A few comments below have asked if the trigger is being deleted or just disabled or not working. As I stated, it's being deleted completely. Also, the problem is not related to the actual contents of the trigger. As I stated, it I remove the contents and replace with some extremely simple code that doesn't do anything then it is still deleted.

Look for table drops -- you might be surprised at what actually causes your table to be dropped. (Different things you do through the SSMS UI, as opposed to using tsql directly.)

You can prevent this kind of inadvertant table dropping like this: Tools -> Options -> Designers-> Uncheck "Prevent saving changes that require table re-creation"

Perhaps you could create a DDL trigger on the database and log all the object deletion statements. In particular, you could write the drop events to a table to log to trace it or just flat out block it.

Is other application code dropping and re-creating the entire table ? If so, perhaps it is not aware of the trigger.

Thanks for the suggestions everyone. In particular Billinkc's suggestion of creating a DDL trigger was cool. I didn't know that such a thing exists.

Anyway after three months of wondering what was going on here I finally got to the bottom of it.

I have a bunch of scripts to create various triggers. They have the typical format of "if trigger exists then delete", "go", followed by "create trigger" and another "go". I use a script to add all these individual triggers to the database in one go. I missed one of the "go" commands at the bottom of a create command (in all these hundreds of lines). This means that trigger A got added to the database with "if trigger B exists then delete" at the bottom of it. That was so confusing...