SQL FOR 和 AFTER 触发器之间的区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5340638/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Difference between a FOR and AFTER triggers?
提问by sqlchild
What's the difference between a FOR and AFTER triggers?
FOR 和 AFTER 触发器之间有什么区别？
回答by Ben
There is no difference, they do the same thing.
没有区别，他们做同样的事情。
CREATE TRIGGER trgTable on dbo.Table FOR INSERT,UPDATE,DELETE
Is the same as
是相同的
CREATE TRIGGER trgTable on dbo.Table AFTER INSERT,UPDATE,DELETE
An INSTEAD OFtrigger is different, and fires before and instead of the insert and can be used on views, in order to insert the appropriate values into the underlying tables.
一个INSTEAD OF触发是不同的，和火灾和前代替插入件，并且可以在视图中使用，以将相应的值到底层表。
回答by Waqas Raja
@Benis absolutely right.
@Ben是绝对正确的。
Here is MSDN article Exploring SQL Server Triggers
这是 MSDN 文章探索 SQL Server 触发器
A paragraph from the article:
文章中的一段话：
That syntax is also acceptable in older versions of SQL Server. However, now that there are two types of triggers in SQL Server 2000, I prefer to refer to FOR triggers as AFTER triggers. Thus, for the remainder of this article I will refer to either AFTER or INSTEAD OF triggers.
Like the AFTER trigger you saw earlier, this trigger prevents changes from being made to the lastname field. However, it implements this business rule differently than the previous example. Because the INSTEAD OF trigger fires in place of the UPDATE statement, the INSTEAD OF trigger then evaluates if the business rule test passes or not. If the business rule test passes, in order for the update to occur the INSTEAD OF trigger must explicitly invoke the UPDATE statement again.
该语法在旧版本的 SQL Server 中也是可以接受的。但是，既然 SQL Server 2000 中有两种类型的触发器，我更喜欢将 FOR 触发器称为 AFTER 触发器。因此，对于本文的其余部分，我将参考 AFTER 或 INSTEAD OF 触发器。
与您之前看到的 AFTER 触发器一样，此触发器可防止对姓氏字段进行更改。但是，它实现此业务规则的方式与前一个示例不同。因为 INSTEAD OF 触发器代替 UPDATE 语句触发，所以 INSTEAD OF 触发器然后评估业务规则测试是否通过。如果业务规则测试通过，为了进行更新，INSTEAD OF 触发器必须再次显式调用 UPDATE 语句。
回答by Nour El-Hoda
AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires. AFTER is the default when FOR is the only keyword specified.
AFTER 指定仅当触发 SQL 语句中指定的所有操作都已成功执行时才触发 DML 触发器。在触发此触发器之前，所有引用级联操作和约束检查也必须成功。当 FOR 是唯一指定的关键字时，AFTER 是默认值。
AFTER triggers cannot be defined on views.
无法在视图上定义 AFTER 触发器。
INSTEAD OF Specifies that the DML trigger is executed instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements. INSTEAD OF cannot be specified for DDL or logon triggers.
INSTEAD OF 指定执行 DML 触发器而不是触发 SQL 语句，因此覆盖触发语句的操作。不能为 DDL 或登录触发器指定 INSTEAD OF。
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql
