pl sql trigger total

Question

I want to create a trigger to calculate the subtotal for each row that is inserted into SALESORDERDETAIL. Subtotal is quantity * price. Can somebody please help me understand how to go about this?

I believe all of the necessary information is in the SALESORDERDETAIL. I have used a trigger in conjunction with a sequence before, but I have not used trigger for anything else. Do I need a procedure and execution each time? How do I write those? Any help is appreciated as I’ve been stuck on this for the last day. Thank you!

drop table SALESORDERDETAIL;

create table SALESORDERDETAIL (
SONo CHAR(9), -- sales order number
ItemID char(8), -- item being ordered (finished goods)
SOquantity number(5), -- quantity of the item being ordered
Price Number(10,2), -- unit price of each item
subtotal Number(10,2), -- sales order detail (line) subtotal
constraint SOD_pk primary key(SONo,ItemID),
constraint SOD_FG_FK foreign key(ItemID) references FinishedGoods(itemid),
constraint SOD_SO_FK foreign key(SONo) references SalesOrders(SONo)
);

Insert into SALESORDERDETAIL (SONO,ITEMID,SOQUANTITY,PRICE,SUBTOTAL) values
('SO1000001','FG000001',100,10,'');
Insert into SALESORDERDETAIL (SONO,ITEMID,SOQUANTITY,PRICE,SUBTOTAL) values
('SO1000001','FG000002',50,2,'');

commit;

Not relevant to your question, but I would recommend never using `char` for anything. Strings are `varchar2` unless they exceed the length limit and need to be `clob`. — William Robertson, Nov 29 '17 at 19:37
Thank you. Good to know! The tables were pre- created as it's part of a lecture I'm trying to grasp. — T la, Nov 29 '17 at 19:50
`char` vs `varchar2`: https://stackoverflow.com/a/42165653/230471 — William Robertson, Nov 29 '17 at 19:55

Andrei Nossov · Accepted Answer · 2017-11-29T22:19:48.860

1

Assuming this is Oracle? If so, consider a virtual column instead of a trigger:


...
subtotal     AS (soquantity * price),
...

... but if you still want a trigger, it would look somewhat like this:

CREATE OR REPLACE TRIGGER SALESORDERDETAIL_before
BEFORE INSERT OR UPDATE
 ON SALESORDERDETAIL
 FOR EACH ROW
BEGIN
 :new.subtotal := :new.soquantity * :new.price;
END;
/

I'm not a fan of less elegant solutions, but looks like it's for education, not for problem solving, so good luck.

edited Nov 29 '17 at 22:19

answered Nov 29 '17 at 19:33

Andrei Nossov

101
4

Thank you, but looking to utilize trigger in this situation. – T la Nov 29 '17 at 19:49
I had this and thought I was wrong because I am getting null values all the way down when I do select * from salesorderdetail; how else should I test this out? – T la Nov 30 '17 at 04:05
You did create the trigger before inserting data, correct? It's not retroactive. – Andrei Nossov Nov 30 '17 at 22:57

pl sql trigger total

1 Answers1