什么时候触发火和什么时候不是他们 [英] 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.

推荐答案

您希望他们何时启动？

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 表。更新的行在两者中列出。这会抛出很多人，因为他们不习惯将更新视为 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文档实际上非常深入地讨论了触发器何时触发， a href =https://msdn.microsoft.com/en-us/library/ms189799(v=sql.130).aspx =nofollow noreferrer>此处。

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

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