在 SQL Server 中创建触发器 [英] Create Trigger in SQL Server

问题描述

当我想使用 SQL Server 2008 R2 的预定义CREATE TRIGGER"创建触发器时，我迷路了.能否请您给我一个可以用来创建触发器的直接 SQL 语句，并告诉我如何定义 AFTER、BEFORE 等等?

I got lost when I wanted to create trigger using the pre-defined "CREATE TRIGGER" of SQL Server 2008 R2. Could you please give me a direct SQL statement that I can use to create a trigger, and tell me how to define AFTER, BEFORE, and all that?

另外，我如何知道 UPDATED/INSERTED/DELETED 行，并使用它们的列值在触发器内部执行操作?

Also, how can I know the rows UPDATED/INSERTED/DELETED, and use their column values to do operations inside the trigger?

推荐答案

数据库是面向集合的，触发器也不例外.执行给定操作时将触发触发器，并且该操作可能会影响多行.因此，问题 说我想知道该行的主键" 是用词不当.可以插入多行.

Databases are set-oriented and triggers are no different. A trigger will fire when a given operation is performed and that operation might affect multiple rows. Thus, the question "Say I want to know the Primary Key of that row" is a misnomer. There could be multiple rows inserted.

SQL Server 为名为 inserted 和 deleted 的 AFTER 触发器提供了两个特殊表，它们表示由操作插入或删除的行，并且结构与被删除的表相同.做作的.更新触发器可能会同时填充 inserted 和 deleted 而插入触发器只会填充 inserted 表.

SQL Server provides two special tables for AFTER triggers named inserted and deleted which represent the rows that were inserted or deleted by an action and are structured identically to the table being affected. An update trigger might populate both inserted and deleted whereas an insert trigger would only populate the inserted table.

来自评论:

但是电子邮件收件人将根据第二个表中的值来决定，其中外键 ID 位于第一个表中(即带有触发器的表)

but the email recipient will be decided based on a value in a second table, where the foreign key ID is located in the first table (which is the one with trigger

这个问题的答案是使用 inserted 表(同样，您必须假设它可能有多行)循环浏览行并发送电子邮件.但是，我建议不要将电子邮件逻辑放在触发器中.相反，我建议将该逻辑放在一个存储过程中，然后从中发送电子邮件.

The answer to this question is to use the inserted table (which again, you must assume could have multiple rows) to cycle through the rows and send an email. However, I would recommend against putting email logic in a trigger. Instead, I would recommend putting that logic in a stored procedure and send your email from that.

参考:创建触发器

这篇关于在 SQL Server 中创建触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！