我的 SQL Server 2008 触发器是如何被删除的？
这是一个非常奇怪的问题。我之前在这里问过这个问题： 我的触发器是如何被删除的？
我重命名了我的触发器，我认为它已经不再发生，但问题似乎又回来了。
我已向数据库中的表添加了一个触发器。服务器是 SQL 2008。触发器不会做任何特别棘手的事情。当某些字段发生更改时，只需更改表中的 LastUpdated 字段。这是“更新后”触发器。
有一个大型 C++ 遗留应用程序针对该数据库运行各种大型查询。不知何故（我完全不知道如何）它正在删除这个触发器。它不会删除任何其他触发器，并且我确信它不会显式删除触发器或表。这个应用程序的开发人员甚至不知道我的触发器。
这怎么可能？
我尝试使用 SQL Server Profiler 运行跟踪，并检查了它发送的每个命令并使用 SQL Management Studio 运行它们，但我的触发器不受影响。这似乎只在我运行应用程序时发生。
其他开发人员认为他们没有明确删除它。它不存在于 sys.objects 或 sys.triggers 中，因此这不是 SSMS 的故障。我想我会重新命名它并希望得到最好的结果？我想不出还有什么可以尝试的。下面的一些评论询问触发器是否被删除或只是禁用或不起作用。正如我所说，它正在被完全删除。此外，该问题与触发器的实际内容无关。正如我所说，我删除了内容并替换为一些极其简单的代码，但这些代码不执行任何操作，然后它仍然被删除。
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 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.
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
查找表删除——您可能会对实际导致表删除的原因感到惊讶。 （与直接使用 tsql 不同，您可以通过 SSMS UI 执行不同的操作。）
您可以像这样防止这种无意的表删除：
工具->选项->设计师->取消选中“防止保存需要重新创建表的更改”
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"
也许您可以在数据库上创建一个 DDL 触发器 并记录所有对象删除语句。特别是，您可以将放置事件写入表中以记录到追踪它或直接阻止它。
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.
谢谢大家的建议。特别是 Billinkc 创建 DDL 触发器的建议很酷。我不知道有这样的事情存在。
不管怎样，经过三个月的思考，我终于弄清楚了这里到底发生了什么。
我有一堆脚本来创建各种触发器。它们的典型格式是“如果触发器存在，则删除”、“go”，后跟“创建触发器”和另一个“go”。我使用一个脚本一次性将所有这些单独的触发器添加到数据库中。我错过了创建命令底部的“go”命令之一（在所有这数百行中）。这意味着触发器 A 被添加到数据库中，并且底部带有“如果触发器 B 存在则删除”。这太令人困惑了......
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...