CODEWITHSUNDEEP
sqlsql-servertriggers
Aaron
Aaron

Reputation: 165

How do I link another table into my trigger?

I'm new to SQL but I'm trying to create a trigger that checks that prices are appropriately changed. However, one of the columns I need comes from another table and I'm not quite sure how to declare that variable. This is my current attempt.

The ACCESSORY table is a child of the PRODUCT table being connected with ProductCode and has only 1 other column called Class which stands for classification. The trigger I created is currently for the PRODUCT table as that is where all the prices are recorded.

CREATE TRIGGER PriceCheck1
ON PRODUCT
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ProductCode AS Char(6),
            @ProductName AS VarChar(50),
            @StockLevel  AS Int,
            @OldPrice    AS Deciaml(10,2), @NewPrice AS Deciaml(10,2),
            @ProductType AS VarChar(9)
            --@Class       AS VarChar(20)

    SELECT  
        @ProductCode = ProductCode, @ProductName = ProductName,
        @StockLevel = ProductStockLevel, @NewPrice = ProductPrice,
        @ProductType = ProductType --@Class = Class
    FROM    
        inserted;
    --FROM    #TempTable

    SELECT  
        @ProductCode = ProductCode, @ProductName = ProductName,
        @StockLevel = ProductStockLevel, @OldPrice = ProductPrice,
        @ProductType = ProductType
    FROM    
        deleted;

    IF UPDATE(ProductPrice)
    BEGIN
        IF (@ProductType = 'Food Item' AND @NewPrice > 200)
        BEGIN
            RAISERROR('The price of food item cannot exceed $200.', 16, 1)
            ROLLBACK TRANSACTION
        END
        ELSE IF (@ProductType = 'Accessory' AND @NewPrice < 50)--AND @Class = 'Bed and Bedding'
        BEGIN
            RAISERROR('The price of any accessory with a classification that includes the word bed cannot be less than $50.', 16, 1)
            ROLLBACK TRANSACTION
        END
        ELSE
            PRINT 'Price of Product Code ' + @ProductCode + ' has been changed from $' + Cast(@OldPrice AS nVarChar(10)) + ' to $' + Cast(@NewPrice AS nVarChar(10)) + '.'
    END
END

The trigger works fine, but now when I tried to bring the accessory table in, the trigger doesn't even fire when i update the table. Also is there a way where i can print out the prices with 2 decimal places? Currently, they throw back the error that it is unable to convert it to decimal from NVarChar even when I used cast.

Upvotes: 0

Views: 273

Answers (1)

marc_s
marc_s

Reputation: 755043

Okay, here's a set-based approach that takes into account that an update of more than 1 row at once will only fire the trigger once:

CREATE TRIGGER PriceCheck1
ON PRODUCT
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- check if we have any rows that the "ProductPrice" was updated 
    -- for a "Food Item" and a new price of over $200
    IF EXISTS (SELECT * 
               FROM Inserted i
               INNER JOIN Deleted d ON i.PrimaryKeyColumn = d.PrimaryKeyColumn
               WHERE
                   i.ProductPrice <> d.ProductPrice   -- ProductPrice was updated
                   AND i.ProductType = "Food Item"    -- for a "Food Item"
                   AND i.ProductPrice > 200           -- new price over $200
    BEGIN
        RAISERROR('The price of food item cannot exceed $200.', 16, 1)
        ROLLBACK TRANSACTION
    END;

    -- check if we have any rows that the "ProductPrice" was updated 
    -- for a "Accessory" and a new price of under $50
    IF EXISTS (SELECT * 
               FROM Inserted i
               INNER JOIN Deleted d ON i.PrimaryKeyColumn = d.PrimaryKeyColumn
               WHERE
                   i.ProductPrice <> d.ProductPrice   -- ProductPrice was updated
                   AND i.ProductType = "Accessory"    -- for a "Food Item"
                   AND i.ProductPrice < 50            -- new price under $50
    BEGIN
        RAISERROR('The price of any accessory with a classification that includes the word bed cannot be less than $50.', 16, 1)
        ROLLBACK TRANSACTION
    END;
END

You need to join the Inserted and Deleted pseudo tables on the primary key column (which I don't know which it is - you didn't specify that in your question - so please replace PrimaryKeyColumn with the actual primary key column's name).

Also, I left out the PRINT statement, since in a trigger, you cannot have any UI interaction - that PRINT goes into the digital nirvana without being useful to anyone.

Upvotes: 2

Related Questions