0

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 . enter image description here

1

1 Answer 1

0

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 $$
2
  • Can you tell me what is new.stationery_code in your solution
    – Deeva
    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
    – Eralper
    Oct 24, 2020 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.