Calculating the record value

Question

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 .

Answer 1

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 $$