什么时候触发火，什么时候不触发 [英] When do triggers fire and when don't they

问题描述

关于 SQL Server 2005 中触发器的一般性问题.

Pretty general question regarding triggers in SQL server 2005.

在什么情况下会触发表触发器，在什么情况下不会触发?

In what situations are table triggers fired and what situations aren't they?

任何用于演示的代码示例都很棒.

Any code examples to demonstrate would be great.

我正在编写一个基于审计的数据库，只想了解可能不会触发我为更新、删除和插入表设置的触发器的任何情况.

I'm writing a audit based databases and just want to be aware of any situations that might not fire off the triggers that I have set up for update, delete and insert on my tables.

我的意思的一个例子，

UPDATE MyTable SET name = 'test rows' WHERE id in (1, 2, 3);

以下语句只触发一次更新触发器.

The following statement only fires the update trigger once.

推荐答案

你希望他们什么时候开火?

When do you want them to fire?

CREATE TRIGGER AFTER ACTION

在提交操作 (insert update delete) 后运行.INSTEAD OF 触发触发器代替操作.

That runs after the action (insert update delete) being committed. INSTEAD OF fires the trigger in place of the action.

触发器的最大问题之一是，只要执行操作，它们就会触发，即使没有行受到影响.这不是错误，如果您不小心，它会很快烧伤您.

One of the biggest gotchas with triggers is that they fire whenever an action is performed, even if no rows are affected. This is not a bug, and it's something that can burn you pretty quickly if you aren't careful.

此外，对于触发器，您将使用 inserted 和 deleted 表.更新的行列在两者中.这让很多人望而却步，因为他们不习惯将 update 视为 delete 然后 insert.

Also, with triggers, you'll be using the inserted and deleted tables. Updated rows are listed in both. This throws a lot of folks off, because they aren't used to thinking about an update as a delete then insert.

MSDN 文档实际上对触发器何时触发以及它们有什么影响进行了非常深入的讨论 这里.

The MSDN documentation actually has a pretty in-depth discussion about when triggers fire and what effect they have here.

这篇关于什么时候触发火，什么时候不触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！