使用有关表更新的日志信息创建触发器

I need to create a trigger which inserts into another table information about price changes. Below I present my solution.

我需要创建一个触发器，将有关价格变化的信息插入另一个表中。下面我介绍我的解决方案。

CREATE TABLE Production.Products_AUDIT  
(
    auditid     INT         NOT NULL IDENTITY,
    productid   INT         NULL,
    old_price   MONEY       NOT NULL,
    new_price   MONEY       NOT NULL,

    CONSTRAINT PK_Products_AUDIT PRIMARY KEY(auditid),
    CONSTRAINT FK_Products_AUDIT_AUDIT 
        FOREIGN KEY(productid) REFERENCES Production.Products(productid)
);

INSERT INTO Production.Products_AUDIT VALUES (1, 18 , 20)
INSERT INTO Production.Products_AUDIT VALUES (2, 19 , 31)

DELETE FROM Production.Products_AUDIT

SELECT unitprice 
FROM Production.Products_AUDIT as p1 
INNER JOIN Production.Products as p2 on p1.productid = p2.productid 

CREATE TRIGGER trig1
ON Production.Products
FOR UPDATE
AS
    declare @prodId INT 
    declare @oldPrice MONEY
    declare @newPrice MONEY

    SET @prodId = (SELECT i.productid 
                   FROM inserted as i 
                   INNER JOIN  Production.Products as pp on i.productid = pp.productid )
    SET @oldPrice = (SELECT i.unitprice 
                     FROM deleted as i 
                     INNER JOIN Production.Products as pp on i.productid = pp.productid )

    SET @newPrice = (SELECT i.unitprice 
                     FROM inserted as i 
                     INNER JOIN Production.Products as pp on i.productid = pp.productid)

    INSERT INTO Production.Products_AUDIT 
    VALUES(@prodId, @oldPrice, @newPrice)

    UPDATE Production.Products 
    SET unitprice = 45 
    WHERE productid < 2

    SELECT * FROM Production.Products_AUDIT

Everything is OK when I update only one record. The problem is when I try to update many records, then I see the error below:

当我只更新一条记录时，一切都很好。问题是当我尝试更新许多记录时，我看到下面的错误：

Msg 512, Level 16, State 1, Procedure trig1, Line 41
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

消息512，级别16，状态1，过程trig1，行41子查询返回的值超过1。当子查询遵循=，！=，<，<=，>，> =或子查询用作表达式时，不允许这样做。该语句已终止。

Does anyone know how to fix this problem?

有谁知道如何解决这个问题？

2 个解决方案

#1

The problem is that Triggers are fired on a statement bases, and not on a row bases. This means that your trigger is fired once for all the rows updated in your statement, so the inserted and deleted tables might contain more than one row.

问题是触发器是在语句库上触发的，而不是在行基础上触发的。这意味着对语句中更新的所有行触发一次触发器，因此inserted和deleted表可能包含多行。

However, your trigger code does not take that into consideration, thus raising an error.

但是，您的触发器代码不会考虑这一点，从而引发错误。

Try this instead:

试试这个：

CREATE TRIGGER Products_ForUpdate
ON Production.Products
FOR UPDATE
AS

    INSERT INTO Production.Products_AUDIT 
    SELECT i.productid, d.unitprice, i.unitprice
    FROM inserted as i 
    INNER JOIN  Production.Products as pp on i.productid = pp.productid
    INNER JOIN deleted as d ON pp.productid = d.productid

#2

The trigger is fired for each Update statement not for each row in an update statement. You do not need any of these variables at all, just select data (old and New) data from inserted and deleted tables and insert it into the audit table directly, something like this........

为每个Update语句触发触发器，而不是为update语句中的每一行触发。您根本不需要任何这些变量，只需从inserted和deleted表中选择数据（旧的和新的）数据并将其直接插入到审计表中，如下所示........

CREATE TRIGGER trig1
ON Production.Products
FOR UPDATE
as
BEGIN
 SET NOCOUNT ON;

    INSERT INTO Production.Products_AUDIT (productid , Old_Price , New_Price)
    SELECT pp.productid 
         , d.unitprice AS OldPrice
         , i.unitprice AS NewPrice
    FROM Production.Products as pp 
    INNER JOIN inserted i ON i.productid = pp.productid
    INNER JOIN deleted  d ON d.productid = pp.productid 

END

#1