I'm new to sql and I can't figure out this trigger.
First Table is Order_Details which has Three rows,
O_Id
F_Id
Price(Int)
Second Table is Payment_Details which has Two rows,
O_Id
Total_Price(Int)
When a order is placed, the Order_Details cretes multiple rows with different foods(F_Id) with different price (Price) but the same Order number(O_Id). Because a single order can have multiple food items
So I need a Trigger to calculate the sum of all the Food Price(Total_Price) in a Single Order.
Is there any possible trigger for this?
CodePudding user response:
If these are really the only columns you would do better to create it as a view.
CREATE TABLE Order_Details( O_Id int, F_Id int, Price Int);
CREATE VIEW Payment_Details AS SELECT O_Id, SUM(Price) AS Total_Price FROM Order_Details GROUP BY O_Id;
insert into Order_Details values (1,1,20),(1,2,15);
select * from Payment_Details;
O_Id | Total_Price ---: | ----------: 1 | 35
db<>fiddle here