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