That is I have a few things to sell, these things and to be a total sales table tot_mat
create trigger sales_item_trigger on sales_item
For insert, update, delete
As
The begin
Declare @ temp int
Select @ temp=(select sum (qty * unit_price) from sales_item group by order_no)
Update the sales set tot_amt=@ temp
Where the sales. Order_no in (select inserted. Order_no from inserted)
Or sales. Order_no in (select does. Order_no from does)
End
But I after inserting data show
Message 512, level 16, state 1, process sales_item_trigger, line 6 [batch starting line 68]
Subquery returns the value of more than one, when the query to follow in=,!=, & lt; , & lt;=, & gt; ,>=, or the subquery is used as the expression, the situation is not allowed,
The statement has been terminated,
Can you tell me what went wrong
CodePudding user response:
- you have a group by the subquery, a variable you save is not the amount of multiple orders!
Create the trigger sales_item_trigger on sales_item
For insert, update, delete
As
The BEGIN
- according to the inserted and does the order number, calculate the total amount of each order, and then update to the corresponding to the total amount of sales
UPDATE a
The SET tot_amt=b.a mt
FROM the sales of a INNER JOIN
(
The SELECT c.o rder_no, SUM (qty * unit_price) AS amt from sales_item c INNER JOIN
(
The SELECT order_no from inserted
The UNION
The SELECT order_no from does
D) ON c.o rder_no=d.o rder_no
GROUP by c.o rder_no
B) ON a.o rder_no=b.o rder_no
End