I'm creating a trigger that grabs information from multiple tables and calculates an "orderTotal".

My trigger does calculate the correct orderTotal, but it updates orderTotal for all rows in "RestaurantOrder" with the latest value. I only want it to update the row with the corresponding orderID.

Here's the code:

CREATE TRIGGER totalFeeOrder
   ON orderDetails
   AFTER INSERT
   AS
   BEGIN
           IF EXISTS (SELECT orderID FROM INSERTED)
           BEGIN
                UPDATE RestaurantOrder 
                SET orderTotal = 
                (SELECT (RO.orderDeliveryFee SUM(OD.itemQuantity*i.itemPrice)) FROM orderDetails as OD
                JOIN Item as I on I.itemID = OD.itemID
                JOIN RestaurantOrder as RO on RO.orderID = OD.orderID
                WHERE RO.orderID in (SELECT orderID from INSERTED)
                and OD.orderID in (SELECT orderID From INSERTED)
                GROUP BY RO.orderID, RO.orderDeliveryFee)
           END
   END;

I tried adding a WHERE clause after the SELECT and before the JOIN to specify that it's only for the INSERTED orderID, but it doesn't allow me to.

What am I missing?

CodePudding user response：

I believe the following is what you require (untested of course).

You don't need the initial exists - rows are only updated for successful joins. You don't need to join back to the OrderDetails table since all the inserted rows exist in the inserted table.

with t as (
    select i.orderID, Sum(i.itemQuantity * it.itemPrice) tot
    from inserted i
    join item it on it.itemId = i.itemId
    group by i.orderID
)
update ro set ro.OrderTotal = ro.OrderDeliveryFee   t.tot
from t
join RestaurantOrder ro on ro.orderID = t.orderID

Edit - in case you have multiple existing rows per OrderID you can include in the expression like so

with t as (
    select od.orderID, Sum(od.itemQuantity * it.itemPrice) tot
    from (select distinct OrderId from inserted) i
    join OrderDetails od on od.orderID = i.orderID
    join item it on it.itemId = od.itemId
    group by od.orderID
)
update ro set ro.OrderTotal = ro.OrderDeliveryFee   t.tot
from t
join RestaurantOrder ro on ro.orderID = t.orderID

CodePudding user response：

Instead of using another column and a trigger, I recommend you keep you database properly normalized by instead using an indexed view. This is a view that the server will materialize to disk, and will maintain along with normal indexes on every update to the base tables.

Unfortunately, there are a number of restrictions on them, primarily:

• It must be schema-bound.
• Only INNER JOIN is allowed.
• For a grouped view, you must include COUNT_BIG(*) and the only other aggregate allowed is SUM
• I recommend always using WITH (NOEXPAND) when querying the indexed view, for performance reasons.
• You cannot have calculated columns with the SUM, so to create the full total you will need a second non-indexed view that references the first. This also has the benefit of enforcing the NOEXPAND hint.

CREATE OR ALTER VIEW dbo.vRestaurantOrder_Indexed
WITH SCHEMABINDING AS

SELECT
  RO.orderID,
  RO.orderDeliveryFee,
  SubTotal = SUM(OD.itemQuantity * I.itemPrice),
  ItemCount = COUNT_BIG(*)  -- must have count in a grouped view
FROM dbo.OrderDetails as OD
JOIN dbo.Item as I on I.itemID = OD.itemID
JOIN dbo.RestaurantOrder as RO on RO.orderID = OD.orderID
GROUP BY
  RO.orderID,
  RO.orderDeliveryFee;

Then you can create an index on it

CREATE UNIQUE CLUSTERED INDEX vRestaurantOrder_Indexed_CX
  ON dbo.vRestaurantOrder_Indexed (orderID);

Then the secondary view:

CREATE OR ALTER VIEW dbo.vRestaurantOrder
AS
SELECT
  RO.orderID,
  RO.orderDeliveryFee,
  RO.SubTotal,
  RO.ItemCount,
  orderTotal = RO.orderDeliveryFee   RO.SubTotal
FROM dbo.vRestaurantOrder_Indexed AS RO WITH (NOEXPAND);