How did my trigger get deleted?

If you can figure out this one you are a true SQL guru! It's one of the weirdest things I've ever seen.

I've added a trigger to a table in our database. The server is SQL 2008. The trigger 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. WTF :(

UPDATE:

Sorry I don't want to waste your time. I just realised that if I change the name of the trigger then it doesn't get deleted. Furthermore if I modify the trigger so it doesn't do anything at all then it still gets deleted. From this I can only guess that the other devs are explicitly deleting it but I've searched the trace for the trigger name and it's not there. I'll hassle them and see what they say. Thanks for the suggestions.

UPDATE 2:

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. So confused :( Guess I'll just rename it and hope for the best? 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.

Cheers

Mark

---

Thoughts:

• To delete a trigger requires ALTER permission = shouldn't be used by an app
• Triggers can be disabled with ALTER TABLE
• Triggers can be confused by testing for @@ROWCOUNT at the beginning to trap dummy updates etc
• Is the trigger coded for single rows only and appears not to run
• Does the trigger exists in sys.objects/sys.triggers: don't rely on Object Explorer in SSMS
• A trigger can be deleted if the table is dropped and re-created
• A trigger won't fire for TRUNCATE TABLE

---

I had an identical issue which I tracked down to a creation script missing a final GO statement.

Script 1

IF EXISTS (....)
    DROP PROC MyProc
GO

CREATE PROC MyProc
.....
/* GO statement is missing */

Script 2

IF EXISTS (....)
    DROP TRIGGER MyDisappearingTrigger
GO

CREATE TRIGGER MyDisappearingTrigger
.....
GO

When I inspected MyProc in the object explorer it looked like this:

CREATE PROC MyProc
AS
...

IF EXISTS (....)
    DROP TRIGGER MyDisappearingTrigger
GO

So this meant that every time the stored proc was called the trigger was also deleted.

---

check with MERGE command in table it will cause triggers get error