在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?

推荐答案

数据库是面向集合和触发器没有什么不同。当执行给定的操作时，触发器将触发，并且该操作可能会影响多行。因此，问题 $code>说我想知道该行的主键 是不正确的。可能插入了多行。

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为AFTER触发器提供了两个特殊的表，分别称为 insertted 和 deleted 表示由操作插入或删除的行，其结构与受影响的表相同。更新触发器可能同时填充插入的和已删除，而插入触发器仅填充插入的表。

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

这个问题是使用 insertted 表（同样，您必须假设可能有多行）在各行之间循环并发送电子邮件。但是，我建议不要放置电子邮件逻辑在触发器中。相反，我建议将该逻辑放入存储过程中并从中发送电子邮件。

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屋！