I have made stored procedure that will update product quantity as per product id.

That works fine for me.

Following is code :


CREATE DEFINER=`root`@`localhost` PROCEDURE `updatequantity`(IN id int, IN qty int)
    update product_master 
    set prod_qty = (prod_qty - qty)
    where prod_id = id;

it works greatly for me. Now I want to put trigger on product_master table that will first check that enough quantity is there or not in stock ?

Suppose order quantity is 100 and total available product quantity is 50 then procedure should not be called instead some error message should be displayed. If enough quantity is there then only stored procedure should be called.

Anyone have idea that how to proceed for that ?

Thank You.

1 Answer 1


Instead of creating trigger you can use below code, It will also return you success or failure message.

DROP FUNCTION IF EXISTS to_updatequantity;

CREATE FUNCTION to_updatequantity(id int, qty int) RETURNS CHAR(100) DETERMINISTIC

    SELECT prod_qty INTO @var_prod_qty FROM product_master WHERE prod_id = id;

    IF (@var_prod_qty > qty) THEN
        UPDATE product_master SET prod_qty = prod_qty - qty WHERE prod_id = id;
        SET @msg = 'prod_qty is updated successfully';
        SET @msg = 'prod_qty is less than or equal to ordered qty'; 
    END IF;  

RETURN @msg;
END //
  • Sir is it possible to display that error message on user page using any mechanism ? Or can we generate any mechanism that ll provide some notification to user that stock is low now. Commented Jan 7, 2014 at 12:57
  • I hope you can call function from your code and assign result to some variable and show it to user, I hope it shouldn't be a problem. For example have a look at stackoverflow.com/questions/12255676/… Commented Jan 7, 2014 at 12:59
  • Yes, Please execute it and have a look, The messages will be displayed are "prod_qty is updated successfully" and "prod_qty is less than or equal to ordered qty" Commented Jan 7, 2014 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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