触发器在另一个表更新时将数据插入新表 [英] Trigger that inserts data into new table when another table is updated

问题描述

我创建了修改订单表，但未在其中插入任何数据。每次有人更新订单表时，应该在此表中创建一个条目。我创建了一个触发器，该触发器指出如果您更新订单表中的一行，则会触发已修改订单表中的条目。它可以正常工作，唯一的问题是我不知道如何获取已更新的订单ID，以便将其输入到修改后的订单表中。

I created a Modified Order table, and did not insert any data into it. This table is supposed to have an entry made every time someone updates the order table. I created a trigger that stated that if you update a row in the Order table it triggers an entry into the Order Modified table. It works properly, the only problem is I do not know how to get the Order ID that was updated to be entered into the Order modified table.

修改后的订单表代码：

Order Modified Table Code:

Create Table Modified_Order (
Modified_Order_Number    Int Identity (1,1),
Modified_Order_Date      Date,
Order_ID                 Int,

Foreign Key (Order_ID) references Orders (Order_ID) 
);

触发代码：

Create Trigger Modified_Order_Trigger
      On Orders After Update
      AS
      Insert Into Modified_Order Values (getdate(),    ) 

;

我遇到的问题是获取已更新的订单的订单ID，以显示在订单修改表。我知道它出现在getdate（）之后留空的位置。我知道这与添加select语句有关，我只是不确定在where语句之后要输入什么，因此它知道要为订单表中更新的任何Order选择订单ID。 b
$ b

The issue I am having is getting the Order ID for the order that was updated to show up in the Order Modified table. I know it comes in the spot that is left blank after getdate(). I know it has something to do with adding a select statement, I'm just not sure what to put in after the where statement so it knows to select the order ID for whatever Order was updated in the order table.

Create Trigger Modified_Order_Trigger
      On Orders After Update
      AS
      Insert Into Modified_Order Values (getdate(), (Select Order_ID
                                                      from Order
                                                        where     ) 

;

谢谢

推荐答案

在触发器内，INSERTED和DELETED虚拟表具有来自两种状态的数据：INSERTED通过INSERT或通过UPDATE，而DELETED通过UPDATE或DELETE已过期，因此语法基本上是：

Within a Trigger, the INSERTED and DELETED virtual tables have the data from both states: INSERTED has new via INSERT or UPDATE, and DELETED has old via UPDATE or DELETE. So the syntax is basically:

Create Trigger Modified_Order_Trigger
   On Orders
   After Update
   AS

     Insert Into Modified_Order (Modified_Order_Date, Order_ID)
       SELECT GETDATE(), Order_ID
       FROM INSERTED

有关触发器的更多信息，请参见此处： http://technet.microsoft.com/en -us / library / ms189799.aspx

For more info on Triggers, look here: http://technet.microsoft.com/en-us/library/ms189799.aspx

这篇关于触发器在另一个表更新时将数据插入新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！