Using Triggers in MySQL to Update

Question

I need to use trigger to check my Update (SalesQty <> 0) but I cannot get it to work

  # TRIGGER - SalesQty has to be bigger than zero   
   DELIMITER //
   CREATE TRIGGER checkzeros BEFORE UPDATE ON Sales
    FOR EACH ROW
    BEGIN
        IF NEW.SalesQty > 0 THEN
           CALL updating(0,'P10005',6565990);
        ELSEIF
           CALL ('Value Has to be Zero')   # not sure how to display error msg
        END IF;
    END//
    DELIMITER ;

   # PROCEDURE TO UPDATE 
   DELIMITER //
   CREATE PROCEDURE updating(IN SQT INT, IN PID Varchar(10), IN SID INT)
   BEGIN
    UPDATE Sales SET SalesQty = SQT WHERE ProductID = PID AND SalesID = SID;
   END//
  DELIMITER ;

CALL updating(1,'PR10005',6565990);  # some sort of recursive error here

I'd appreciate any help

take a look at this: http://stackoverflow.com/questions/24/throw-an-error-in-a-mysql-trigger — Charif DZ, Apr 29 '17 at 07:18
Within your trigger, you are calling `CALL updating(0,'P10005',6565990);` which is the routine which is causing the trigger to be called - which is the recursion bit. If the quantity is greater than zero then you don't need to do anything and let the update go through. — Nigel Ren, Apr 29 '17 at 07:42

Pinke Helga · Answer 1 · 2017-04-29T08:02:22.727

Of course there's a recursion when you update the triggered table within the trigger:

IF NEW.SalesQty > 0 THEN
  CALL updating(0,'P10005',6565990);

Instead use NEW.SalesQty and OLD.SalesQty

SET NEW.SalesQty = 0;

Your logic looks strange at all. It says: If quantity is less than or equal to zero, indicate an error. However, you try to force CALL updating(0,'P10005',6565990); (quantity = 0) otherwise.

You might just want to SIGNAL an exception when NEW.SalesQty = 0 or NEW.SalesQty < 1.

CREATE TRIGGER checkzeros BEFORE UPDATE ON Sales
FOR EACH ROW
BEGIN
    IF NEW.SalesQty < 1 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Quantity must not be below 1';
    END IF;
END//

Using Triggers in MySQL to Update

1 Answers1