我的触发器如何删除？ [英] How did my trigger get deleted?
问题描述
如果您能弄清楚这一点，那么您就是一名真正的SQL专家！这是我见过的最奇怪的事情之一。
If you can figure out this one you are a true SQL guru! It's one of the weirdest things I've ever seen.
我已向数据库中的表添加了一个触发器。服务器是SQL2008。触发器不会做任何特别棘手的事情。更改某些字段后，只需更改表中的LastUpdated字段即可。这是一个更新后触发器。
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.
有一个大型的C ++旧版应用程序，对该数据库运行各种大型查询。某种程度上（我完全不知道如何）它正在删除此触发器。它不会删除任何其他触发器，并且我确定它没有明确删除触发器或表格。这个应用程式的开发人员甚至对我的触发器一无所知。
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.
这怎么可能？
我尝试使用SQL Server Profiler运行跟踪，并且我已经检查了发送的每个命令并使用SQL Management Studio运行它们，但是我的触发器不受影响。这似乎仅在我运行该应用程序时发生。 WTF：（
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 :(
更新：
对不起，我不想浪费您的时间。我只是意识到如果我更改了触发器的名称，那么它就不会被删除；此外，如果我修改了触发器，使其根本不做任何事情，那么它仍然会被删除。据此，我只能猜测其他开发人员正在明确删除
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，但是我已经在跟踪中搜索了触发器的名称，并且它不存在。我会麻烦他们，看看他们怎么说。
UPDATE 2 ：
UPDATE 2:
其他开发人员认为他们没有明确删除它，因为它在sys.objects或sys.triggers中都不存在，因此对于SSMS来说不是小问题。困惑:(猜猜我只是将其重命名并希望达到最佳状态？想不出其他办法尝试了。以下几条评论询问触发器是被删除还是被禁用或不起作用。正如我所说，它是被完全删除了。此外，问题与触发器的实际内容无关。正如我所说，它删除了内容并替换为一些非常简单的代码，这些代码不执行任何操作，然后仍被删除。
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.
干杯
Mark
Cheers
Mark
推荐答案
思路：
- 要删除触发器需要ALTER权限=不应由应用程序使用
- 可以使用ALTER TABLE禁用触发器
- 可以通过测试以下选项来混淆触发器@@ ROWCOUNT在开始时捕获虚拟更新等
- 触发器仅针对单行进行编码，并且似乎不运行
- 触发器是否存在在sys.objects / sys.triggers中：不要依赖SSMS中的对象资源管理器
- 如果删除并重新创建表，则可以删除触发器
- 不会为TRUNCATE TABLE触发触发器
- 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
这篇关于我的触发器如何删除？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
