创建一个触发器，该触发器将在另一个表更新时在一个表中插入记录 [英] Creating a Trigger which will insert record in a table on update of another table
问题描述
假设我有表T1和T2
Suppose I have tables T1 and T2
Columns of T1 -->Value
Columns of T2 -->OldValue NewValue
我需要一个触发器，该触发器将在更新时在T2中插入一条记录T1，我还需要知道旧值和新值，我以前从未使用过触发器，所以有什么可以帮助我的，我该如何创建此触发器。有可能吗，谢谢。
What I require is a trigger which will insert a record in T2 on updation of T1 , I need to know the old value and new value also , I have never used triggers before , so can any help me with this , how do I go about creating this trigger.Is it possible ,thanks.
推荐答案
好，您开始使用 CREATE TRIGGER ：
CREATE TRIGGER NameOfTheTriggerPlease
…
应触发其他操作的表为 T1 ，因此应将触发条件定义为 ON 该表：
The table that should trigger the additional action is T1 so the trigger should be defined ON that table:
CREATE TRIGGER T1OnUpdate /* that's just an example,
you can use a different name */
ON T1
…
演员关于应该调用触发器的时间是 UPDATE ，而更新时间是之后，所以……
The action that the trigger should be invoked on is UPDATE and the timing is AFTER the update, so…
CREATE TRIGGER T1OnUpdate
ON T1
AFTER UPDATE
…
现在是时候介绍触发器的 body 了，即实际上应该由触发器执行。您用 AS 关键字以及后面的语句本身介绍正文。
Now's the time to introduce the body of the trigger, i.e. the statements that should actually be executed by the trigger. You introduce the body with the AS keyword followed by the statements themselves.
在您的情况下，一条语句 INSERT 很明显。不太明显的是我们将如何访问旧值和新值。现在，SQL Server为您提供了两个虚拟表，分别是 INSERTED 和 DELETED ，您可以轻松地猜测前者包含所有
In your case, there would be just one statement, INSERT, which is obvious. What's not so obvious is how we are going to access the old and the new values. Now, SQL Server offers you two virtual tables, INSERTED and DELETED, and you can easily guess that the former contains all the new values and the latter the old ones.
这些表的结构与触发器分配给该表的结构相同，即 T1 。它们仅包含受调用触发器的特定 UPDATE 语句影响的行，这意味着可能有多个。反过来，这意味着您需要在触发器中使用的 T1 表中具有一些主键或唯一列（或一组列）匹配已删除和已插入的行。 （实际上，您可能还需要 T2 表中具有引用 T1 主键的列，因此您以后可以确定 T1 的哪一行存储了 T2 中的哪些值。）
These tables have the same structure as the table the trigger is assigned to, i.e. T1. They only contain rows that were affected by the particular UPDATE statement that invoked the trigger, which means there may be more than one. And that, in turn, means that you need to have some primary key or a unique column (or a set of columns) in your T1 table that you can use in the trigger to match deleted and inserted rows. (In fact, you might also need your T2 table to have a column that would reference the T1's primary key, so you could later establish which row of T1 had which values stored in T2.)
出于这个答案的目的，我将假设有一个名为 PK 的主键列和一个 T2 中的相同名称。然后 INSERT 语句可能看起来像这样：
For the purposes of this answer, I'm going to assume that there's a primary key column called PK and a foreign key column of the same name in T2. And the INSERT statement then might look like this:
CREATE TRIGGER T1OnUpdate
ON T1
AFTER UPDATE
AS
INSERT INTO T2 (PK, OldValue, NewValue)
SELECT i.PK, i.Value, d.Value
FROM INSERTED i INNER JOIN DELETED d ON i.PK = d.PK
最后（但并非最不重要）要记住的一件事：整个 CREATE TRIGGER 语句应该是批处理中的唯一语句，即，在<$之前不应有语句c $ c> CREATE TRIGGER 关键字（但您可以在此处添加注释），并且同样，在 AS 关键字之后的所有内容都被视为触发器的一部分正文（但是，例如，如果您正在SQL Server Management Studio中运行脚本，则可以放置 GO 分隔符以指示语句的结尾）。
One last (but not least) thing to remember: the entire CREATE TRIGGER statement should be the only one in the batch, i.e. there should be no statements preceding the CREATE TRIGGER keywords (but you can put comments there) and, likewise, everything after the AS keyword is considered part of the trigger's body (but you can put the GO delimiter to indicate the end of the statement if you are running the script in SQL Server Management Studio, for instance).
有用的阅读内容：
- CREATE TRIGGER (Transact-SQL)
这篇关于创建一个触发器，该触发器将在另一个表更新时在一个表中插入记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
