T-SQL触发器的基本疑惑
触发器定义中FOR和AFTER有什么区别。使用一种与另一种相比有什么好处？
如果我发出更新 5 行的更新语句，触发器（使用 FOR UPDATE）是否会触发 5 次？如果是这样，有什么方法可以使触发器对整个 UPDATE 语句仅触发一次（即使它更新多行）
是否有机会/情况在“插入”或“删除”中有多于一行" 表在触发器生命周期中的任何时间。如果是这样，我可以对此进行快速示例吗？
谢谢
What is the difference between FOR and AFTER in trigger definition. Any benefits of using one vs another?
If I issue an update statement which updates 5 rows, does the trigger (with FOR UPDATE) fires 5 times? If it is so, is there any way to make trigger fire only once for the entire UPDATE statment (even though it updates multiple rows)
Is there any chance/situation of having more than one row in "inserted" or "deleted" table at any time in a trigger life cycle. If it so, can I have a very quick sample on that?
thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
每批次触发一次，并且在设计时应始终牢记这一点。是的，如果您执行多行更新插入或删除，所有行都将位于插入或删除的表中。例如，该命令
将包含表中所有具有 CA 状态的行，即使其中有 10,000,000 行。这就是为什么触发器测试至关重要以及触发器必须设计为处理多行操作的原因。如果触发器在处理多行方面设计不当，那么适用于一行的触发器可能会导致脱数据库急速停止几个小时，或者如果没有正确设计来处理多行，则可能会导致数据完整性问题。触发器在很大程度上不应依赖于游标或循环，而应依赖于基于集合的操作。如果您将 insert 或 delted 的内容设置为变量，那么您几乎肯定会期望当有人对其执行基于集合的操作时，您的触发器将无法正常工作。
SQL Server 有两种基本类型的 DML 触发器，即在记录放入表后发生的触发器。这些通常也用于更新其他一些表。触发器在取代插入/更新/删除之前，通常用于对插入的表进行特殊处理。重要的是要知道之前触发器不会执行发送到表的操作，如果您仍想删除/更新或插入作为触发器的一部分，则必须将其写入触发器中。
Trigger fire once for each batch and should always be designed with that in mind. Yes if you do a multi-row update insert or delte, allthe rows will be in the inserted or deleted tables. For instance the command
would have all the rows in the table that have a state of CA in them even if it was 10,000,000 of them. That is why trigger testing is critical and why the trigger must be designed to handle multi-row actions. A trigger that works well for one row may bring the deatabase toa screeching halt for hours if poorly designed to handle mulitple rows or could cause data integrity issues if not designed correctly to handle mulitple rows. Triggers should not rely on either cursors or loops for the most part but on set-based operations. If you are setting the contents of inserted or delted to a variable, you are almost certainly expecting one row and yor trigger will not work properly when someone does a set-based operation on it.
SQL Server has two basic kinds of DML triggers, after triggers which happen after the record has been placed in the table. These are typically used to update some other table as well. Before triggers take the place of the insert/update/delete, they are used for special processing onthe table inserted usually. It is important to know that a before trigger will not perform the action that was sent to the table and if you still want to delete/update or insert as part of the trigger you must write that into the trigger.