Does if statement in after insert trigger matters for the performance in SQL Server? How if statement can be used for value validation?

Viewed 45

I'm new to SQL Server, currently taking courses. My question is entirely for educational purposes.

I have an online artshop which includes following tables:

CREATE TABLE artproducts 
(
    prodid INT PRIMARY KEY IDENTITY (1,1),
    catkey INT,
    prodname VARCHAR(50) NOT NULL,
    unitprice NUMERIC(10,2) NOT NULL,
    stocknumbers INT NOT NULL,
    available CHAR(3) NOT NULL DEFAULT 'Yes',
    FOREIGN KEY(catkey) REFERENCES artcategory(catid)
);

CREATE INDEX idx_name ON artproducts(prodname);

INSERT INTO artproducts(catkey, prodname, unitprice, stocknumbers, available)
VALUES (3, 'Autumn landscape', 150, 2, 'Yes'),
       (2, 'Brushes 5 stack', 15.95, 20, 'Yes'),
       (2, 'Canvas 100x200', 40.50, 30, 'Yes'),
       (1, 'Christmas ornament', 1.95, 30, 'Yes'),
       (4, 'Copper carafe from Byzantium', 250.99, 3, 'Yes');

CREATE TABLE artorders 
(
    orderid INT PRIMARY KEY IDENTITY(1,1),
    orderdate DATE NOT NULL,
    prodkey INT NOT NULL,
    custkey INT NOT NULL,
    quantity INT NOT NULL,
    orderprice NUMERIC(10,2) NOT NULL,
    orderstatus VARCHAR(15) DEFAULT 'In progress',
    FOREIGN KEY (prodkey) REFERENCES artproducts (prodid),
    FOREIGN KEY (custkey) REFERENCES artcustomers (custid)
);

I created a trigger in SQL Server 2019 to update the price in artorders with the price from artproducts:

ALTER TRIGGER tr_artshop_FIU_checkprice
ON artorders
FOR INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    UPDATE artorders
    SET orderprice = prod.unitprice
    FROM artproducts AS prod
    INNER JOIN inserted AS i ON prod.prodid = i.prodkey 
    INNER JOIN artorders AS ord ON ord.prodkey = prod.prodid
END;

I tried to do it with if condition - to update the orderprice only when it is different from the one in artproducts. I tried using variables and subqueries, but then the trigger works only for single insert.

ALTER TRIGGER tr_artshop_FIU_checkprice
ON artorders
FOR INSERT, UPDATE
AS

BEGIN

    SET NOCOUNT ON;

    DECLARE @v_price NUMERIC (10,2);
    DECLARE @ord_price NUMERIC (10,2);

    SET @v_price = (SELECT prod.unitprice FROM artproducts AS prod INNER JOIN inserted AS i ON prod.prodid = i.prodkey)         
                        
    
    SET @ord_price = (SELECT ord.orderprice FROM artorders AS ord INNER JOIN inserted AS i ON ord.orderid = i.orderid)
            
    
    IF @ord_price != @v_price
    BEGIN   
      UPDATE artorders
      SET orderprice = prod.unitprice
      FROM artproducts AS prod
      INNER JOIN inserted AS i
      ON prod.prodid = i.prodkey 
      INNER JOIN artorders AS ord
      ON ord.prodkey = prod.prodid 
    END
    
END;

So, my question is whether it is better for performance to have if statement, or it doesn't matter? But if it does, how can I use if condition working for multiple inserts.

Than You very much in advance!!!

1 Answers

It is better for "performance" to not have hidden bugs in your code.

The version with IF is assuming that inserted has only one row. This is a false assumption. In SQL Server, triggers are set-based. This code is broken -- and I really wish SQL Server could find a way to return a syntax error in this case.

Hence, performance has nothing to do with why the first version is better. In practice, I would expect the performance to be better too -- in the cases where the second doesn't generate an error (of the form: scalar subquery returned more than one row).

Related