在 SQL Server 中创建触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3358975/
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
Create Trigger in SQL Server
提问by raladin
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?
当我想使用 SQL Server 2008 R2 的预定义“CREATE TRIGGER”创建触发器时，我迷路了。能否请您给我一个可以用来创建触发器的直接 SQL 语句，并告诉我如何定义 AFTER、BEFORE 等等？
Also, how can I know the rows UPDATED/INSERTED/DELETED, and use their column values to do operations inside the trigger?
另外，我如何知道行 UPDATED/INSERTED/DELETED，并使用它们的列值在触发器内执行操作？
采纳答案by Thomas
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.
数据库是面向集合的，触发器也不例外。执行给定操作时将触发触发器，并且该操作可能会影响多行。因此，这个问题"Say I want to know the Primary Key of that row"用词不当。可以插入多行。
SQL Server provides two special tables for AFTER triggers named insertedand deletedwhich 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 insertedand deletedwhereas an insert trigger would only populate the insertedtable.
SQL Server 为 AFTER 触发器提供了两个特殊的表inserted，deleted它们分别表示由操作插入或删除的行，并且结构与受影响的表相同。更新触发器可能会同时填充两者inserted，deleted而插入触发器只会填充inserted表。
From comments:
来自评论：
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
但是电子邮件收件人将根据第二个表中的值来决定，其中外键 ID 位于第一个表中（即带有触发器的表）
The answer to this question is to use the insertedtable (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.
这个问题的答案是使用inserted表（同样，您必须假设它可能有多行）循环浏览行并发送电子邮件。但是，我建议不要将电子邮件逻辑放在触发器中。相反，我建议将该逻辑放在存储过程中，然后从中发送电子邮件。
For reference: Create Trigger
供参考：创建触发器
回答by mrdenny
The basic syntax is
基本语法是
CREATE TRIGGER YourTriggerName ON dbo.YourTable
FOR|AFTER INSERT, UPDATE, DELETE
AS
BEGIN
/*Put what ever you want here*/
UPDATE AnotherTable
SET SomeColumn = AnotherColumn
FROM inserted | deleted
END
GO
回答by Thomas
A trigger is an event-based process that is "triggered" after a table is changed in some way. This will be on DELETE, UPDATE, INSERT, and so forth. Your BEFORE and AFTER syntax will define whether to run the trigger before or after the event is committed.
触发器是一个基于事件的过程，在以某种方式更改表后“触发”。这将在 DELETE、UPDATE、INSERT 等上进行。您的 BEFORE 和 AFTER 语法将定义是在提交事件之前还是之后运行触发器。
That's the short version. Check out MSDN.
那是简短的版本。查看MSDN。
