建立一個觸發器sales_item_trigger：確保sales中訂單金額，等于sale_item中該訂單的（銷售數量*單價）的總和
也就是我有好幾個東西賣，這些東西的和要成為sales總表的tot_mat
create trigger sales_item_trigger on sales_item
for insert,update,delete
as
begin
declare @temp int
select @temp=(select sum(qty*unit_price) from sales_item group by order_no)
update sales set tot_amt = @temp
where sales.order_no in(select inserted.order_no from inserted)
or sales.order_no in(select deleted.order_no from deleted)
end
但我在插入資料后顯示
訊息 512，級別 16，狀態 1，程序 sales_item_trigger，行 6 [批起始行 68]
子查詢回傳的值不止一個。當子查詢跟隨在 =、!=、<、<=、>、>= 之后，或子查詢用作運算式時，這種情況是不允許的。
陳述句已終止。
請問哪里出了問題
uj5u.com熱心網友回復：
--你的子查詢里有group by ,你一個變數存不了多個訂單的金額呀
create trigger sales_item_trigger on sales_item
for insert,update,delete
as
BEGIN
--按inserted和deleted的訂單號,計算出每個訂單的金額合計,然后更新到對應到sales的總金額
UPDATE a
SET tot_amt = b.amt
FROM sales a INNER JOIN
(
SELECT c.order_no,SUM(qty*unit_price) AS amt from sales_item c INNER JOIN
(
SELECT order_no from inserted
UNION
SELECT order_no from deleted
)d ON c.order_no=d.order_no
GROUP by c.order_no
) b ON a.order_no=b.order_no
end
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/213120.html
標籤：疑難問題
下一篇：sql陳述句一對多關系