CODEWITHSUNDEEP
sql-server
Dawid Malczak
Dawid Malczak

Reputation: 41

Create trigger with log information about table update

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.

Does anyone know how to fix this problem?

Upvotes: 0

Views: 70

Answers (2)

M.Ali
M.Ali

Reputation: 69494

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........

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

Upvotes: 2

Zohar Peled
Zohar Peled

Reputation: 82474

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.

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

Upvotes: 2

Related Questions