如何在Master-Detail上触发

Question

我有以下场景：

CREATE TABLE dbo.Orders
(  
    OrderID int IDENTITY (1,1) NOT NULL  
  , OrderVersion int DEFAULT(1)
  , Customer varchar(30)
  , ScheduleDate date
  , PaymentOption int
);  

CREATE TABLE dbo.OrdersItems
(  
    OrderItemsID int IDENTITY (1,1) NOT NULL  
  , OrderID  int
  , Product  varchar(100)
  , Qty      int 
  , value    decimal(18,2)
);  

CREATE TABLE dbo.logOrders
(  
    OrderID int NOT NULL  
  , OrderVersion int DEFAULT(1)
  , Customer varchar(30)
  , ScheduleDate date
  , PaymentOption int
);  

CREATE TABLE dbo.logOrdersItems
(  
    OrderItemsID int NOT NULL  
  , OrderID  int
  , Product  varchar(100)
  , Qty      int 
  , value    decimal(18,2)
); 

-- Insert values into the table.  
INSERT INTO dbo.Orders (Customer , ScheduleDate, PaymentOption)  
VALUES ('John', 2016-09-01, 1);  

INSERT INTO dbo.OrdersItems( OrderId, Product, Qty, Value)
VALUES (1, 'Foo', 20, 35.658), 
       (1, 'Bla', 50, 100)
       (1, 'XYZ', 10, 3589)

第一条语句

UPDATE Orders set ScheduleDate = 2016-10-05 WHERE OrderId = 1

第二条语句

Delete From OrdersItems WHERE OrderItemsID = 2
UPDATE OrdersItems set Qty = 5 WHERE OrderItemsID = 1

第三条语句

Update Orders set PaymentOption = 2 WHERE OrderId = 1
Update OrdersItems set Value = 1050 WHERE OrderItemsID = 3

我正在尝试弄清楚如何创建触发器，以便在上面的每个语句示例之后在日志表上插入更改前的数据。以及在表订单上将OrderVersion设置为OrderVersion +1。因此，在日志表上，我将拥有后一个版本之后的所有版本。

在执行UPDATE、DELETE、INSERT语句以获取原始数据并在logTables上执行INSERT之前，是否可以使用单个触发器来监视这两个表并执行获取原始数据的操作？

这里有一个示例，可以更好地解释我想要的结果。

This is the Initial Data on table Orders and OrdersItems

如果我在订单(任何列)上进行更新，或者在OrdersItems上进行更新、插入、删除，我需要分别在logTables上插入图像上的数据。这样，我就可以在logOrders和logItems上获得原始数据，并在订单和项目上获得更改后的数据。

我希望我能更好地解释我的意思。

Answer 1

您将需要两个触发器。Orders表的触发器处理Orders表的更新/删除。OrdersItems表的触发器对OrdersItems执行相同的操作。触发器如下所示：

对于Orders表：

CREATE TRIGGER dbo.Orders_trigger 
   ON  dbo.Orders 
   AFTER DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.logOrders
    SELECT * FROM DELETED;

    INSERT INTO dbo.logOrdersItems
    SELECT oi.* FROM OrdersItems oi
    WHERE oi.OrderID IN (SELECT OrderId FROM DELETED);

END
GO

对于OrdersItems：

CREATE TRIGGER dbo.OrdersItems_trigger 
   ON  dbo.OrdersItems 
   AFTER DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    --Inerst the changed/deleted OrdersItems into the log
    INSERT INTO dbo.logOrdersItems
    SELECT * FROM DELETED

    --Inserts the unchanged sibling OrdersItems records into the log
    INSERT INTO dbo.logOrdersItems
    SELECT oi.* FROM OrdersItems oi
    WHERE oi.OrderId IN (SELECT DISTINCT OrderId FROM DELETED)
    AND oi.OrderItemsID NOT IN (SELECT DISTINCT OrderItemsID FROM DELETED);

    INSERT INTO dbo.logOrders
    SELECT o.* FROM Orders o
    WHERE o.OrderID IN (SELECT DISTINCT OrderId FROM DELETED);

END
GO

订单触发器相当简单。使用虚拟删除表将原始版本的记录插入到日志中。然后连接到子OrdersItems记录，并将它们插入到日志中。按照这种编写方式，即使您一次更新或删除多个订单记录，它也可以正常工作。

OrdersItems触发器稍微复杂一点。您需要记录更改前版本的OrdersItems和订单记录。但您也希望(我认为)也记录未更改的“兄弟”OrdersItems记录，以便您对这些记录有一个完整的了解。

我知道这只是您的示例数据，但是您需要为日志表中的记录添加某种时间戳。否则，你只会得到一堆重复的行，并且你不能区分哪个是哪个。在触发器的开头，您可以创建一个变量来保存更新日期时间，然后将其附加到日志的INSERT语句中。

DECLARE @UpdateDateTime DATETIME;
SET @UpdateDateTime = GETUTCDATE();