For the past few days, I am working on my SQL project 'Supermarket Billing Management System', here I am getting a lot of obstacles while creating this project. Since I am a beginner I am not able to solve my all issues, So please help me!!
Here is my doubt:
I have created a table called 'Payments' and inside it, I have even created a trigger and a procedure, Now I don't know how to insert the values in the Payments table because of trigger and procedure. And I want such a procedure that can add the total of the product's price of a single person and it will store into Final Total, I am not sure that my procedure code is right or not, but it was created successfully. So if there is any problem with my Procedure code then please let me know and please also tell me how can I insert it by giving an example
Paymnets table :
create table Paymnets
(
PayId int primary key, PayDate date,
ProdTotal int,
FinalTotal int,
PayOrdId int references orders,
PayProdId int references Products,
PayCustId int references Customers
);
Products table:
create table Products (
ProdId number primary key,
ProdNum number not null unique,
ProdName varchar2(15),
ProdPrice int,
ProdQuantity int,
ProdCustId int references Customers,
ProdOrdId int references Orders,
ProdStoreId int references Stores
);
Procedure :
create procedure FINAL_TOTAL(C IN NUMBER, T OUT NUMBER) IS BEGIN
UPDATE
Payments
SET
FinalTotal = FinalTotal + ProdTotal
WHERE
PayCustId = C;
Commit;
SELECT
FinalTotal into T
FROM
Payments
WHERE
PayCustId = C;
END;
/
Trigger:
create trigger PROD_TOTAL
AFTER INSERT ON Products
BEGIN
UPDATE Paymnets
SET ProdTotal = (SELECT Products.ProdPrice * Products.ProdQuantity FROM Products);
END;
/
insert statement:
insert into Payments values(1,'2020-10-07',1,1,1);
Well, after inserting this many values I knew that I'll get an error and so I got:
error:not enough values
Here I want to know, how can I insert the values in the Payment table and If my procedure code is wrong then what should I write? So please help me in solving these issues!!
question from:
https://stackoverflow.com/questions/65883948/how-to-insert-in-a-table-in-which-trigger-is-also-present 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…