# Calculating the record value

I have two table one tblstationerystock (stationery_name,stationery_code, balance) and other tblstationerytranscation(stationery_code,trsntype,quantity).Trsntype column is transction type consists of two type of value (received or issued) I want to calculate the value of balance in first table based on any transcation for stationery in second table. Mathematically suppose there is a stationery name 'PEN' in first table and in second table there are three record for stationery 'PEN' - STATIONERY. TRSNTYPE QUANTITY Pen. Received. 200 Pen. Issued. 30 Pen. Issued. 40

In first table in balance column it should show 50(100-30-20)

I dont have much knowledge about sql .Is it possiblePlease guide me to achieve this .

Maybe if the MySQL version supports, you can use an AFTER INSERT TRIGGER

Here is the code that will help for INSERT statements

``````    delimiter \$\$
CREATE TRIGGER mytrigger
AFTER INSERT
ON tblstationerytranscation
FOR EACH ROW
BEGIN
declare quan integer;
set quan = new.quantity;

if new.trsntype = 'issued' then
set quan = -1 * quan;
END IF;

update tblstationerystock
SET balance = balance + quan
where stationery_code =  new.stationery_code;

end \$\$
``````
• Can you tell me what is new.stationery_code in your solution Commented Oct 24, 2020 at 16:25
• In your question, the table tblstationerytranscation has the field stationery_code not as stationerycode as in the screenshot. That is the foreign key field in your table referincing to the tblstationerystock table Commented Oct 24, 2020 at 17:17