Trigger on northwind

Question

I have a problem with a trigger which should do this: (On northwind) after insert trigger that calculate the sum of prices in Order Details Table and but it in a new attribute named OrderTotal in Orders Table

I tried to do this but it's not working

CREATE TRIGGER TotalCalc
    ON  dbo.OrderDetails
    AFTER INSERT
AS declare @Price float , @Quan int , @Dis float , @Total float
BEGIN
    SELECT Orders.OrderId From Orders
    Select  @Price = OrderDetails.UnitPrice,
            @Quan = OrderDetails.Quantity,
            @Dis = OrderDetails.Discount,
            @Total = Orders.OrderTotal
FROM         OrderDetails INNER JOIN
                      Orders ON OrderDetails.OrderID = Orders.OrderId
    SET @Total = Sum(@Price * @Quan) - @Dis;

  Insert into Orders.OrderTotal values (@Total)

END
GO

Can anyone show me the way it works.

You have two SELECT statements, and there may be other problems. — Tim Biegeleisen

Alex Kudryashev Alex Kudryashev · Accepted Answer · 2016-06-24T01:59:04

As you can probably guess it doesn't work. A trigger shouldn't return any select (your first select). And triggers suppose to use virtual tables inserted and deleted.
Finally, Insert into Orders.OrderTotal values (@Total) makes no sense. It creates new orders row which doesn't have any details like customerid, employeeid, and so on.
Try to sum linetotals from OrderDetails using orderid from inserted and UPDATE orders.orderTotal.

Trigger on northwind

1 Answers