sql trigger disappears

Question

I've got a trigger in a sql server table. This trigger has disappeared. Is there something systematic that might be causing it to be deleted?

There is no replication on this db.

Answer 1

Recreating the table can drop a trigger. Disabling triggers is also possible. Might it be one of these options?

Answer 2

In addition to what Randolf said, it is possible that some other developer or dba dropped the trigger because it was interfering with what he or she wanted to do. Not everyone understands the ramifications of doing such dumb things so they happen. It would be a good example of the need for more formalized processes and to limit access to production if this is the case.

Hopefully you have the trigger under source control and can easily recreate it.

You might also consider implementing DDL triggers so that you can prevent such things in the future or at least see who did them.

Answer 3

2 options if vanished

• DROP/CREATE TABLE ...
• DROP TRIGGER ...

An option if not working_

• ALTER TABLE ... DISABLE TRIGGER ...

You can see if the table was recreated using this. Of course, it does not tell you who...

SELECT create_date, modify_date FROM sys.objects WHERE name = 'MyTable'

For example, a table change via SSMS GUI that went wrong, can drop a trigger. But there is no systematic process to randomly drop a trigger: it requires an explicit action by someone.