SQL trigger disappears

[ettienne]

MS SQL Server 2012 Standard Edition 64 bit
I have a simple trigger on one table that sets the value of one field based on the values in another field int the same table. The trigger works perfectly - then it just disappears from the table. Things are fine again after the trigger is recreated.
Weird, it just does not make sense. The client is totally clueless about SQL so I don't think they are deleting the trigger somehow, but you never know. The database has never been backed up, let alone restored... something else to raise with the client. My first thought was a restore wiped out the trigger.
Where can I start looking why this is happening?

Sage 300 ERP Certified Consultant

http://www.adssglobal.net

 

[Olaf Doschke]

And the database is something designed by you or exclusively maintained by you? Part of an application by a third party vendor?

That would be essential to know. Applications could ensure the database schema is as needed and unchanged by comparing with meta data and updating via eg DACPAC or tools like red gate sql compare and other such tools.

If you have hands on your solution should be revoking any permissions to alter tables to anybody but your own account.

Bye, Olaf.

 

[gmmastros]

Just thinking out loud... you could add a database ddl trigger that would fire whenever the structure of the database changes. You would want to log this to a table or file somewhere.

To help get you started:

http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ettienne]

Thanks for the input, I will consider the options.
Database Reports > Standard Reports > Schema Changes History shows that all tables were dropped and created 1/20/2015 at 6:34pm.
The triggers were added 1/16/2015, so this user initiated event blew the triggers away.
Now just to figure out who the guilty party is, client denies any wrongdoing as usual. Database is 3rd party accounting application that does not change the schema, unless there is a version upgrade.
I have the same triggers running on other client sites for many years, it's not a software thing maintaining database schema.


Sage 300 ERP Certified Consultant

http://www.adssglobal.net

 

[Olaf Doschke]

OK, obviously someone has too much priviliges in that database, then. Who you can't revoke is of course SA.

Bye, Olaf.