MYSQL after update trigger

MySQLtrigger

I am new to database trigger. I really have no idea why i cant update the Label if the Quantity more than equal to 1000

CREATE TRIGGER TRG_UPDATE_STATUS
AFTER UPDATE ON product
FOR EACH ROW
  BEGIN
    IF NEW.QuantitySold >= 1000 THEN
    SET NEW.Label = 'BestSeller'
    END IF
  END

Here is the trigger I have.

Best Answer

You can only make persistent changes to NEW in a before row trigger. An after trigger doesn't fire until the record has already been written to the table.

To change the data that will be written to the table, you must use a before row trigger. Changing NEW in an after row trigger is possible, but pointless, since the contents of NEW get written to the table before an after trigger executes, as is explained in the documentation.

Related Solutions

MySQL using trigger instead of check

You do not need a RETURN 1 because Trigger is a Stored Procedure, not a Stored Function.

If you want to break it on purpose, that's acceptable.

I wrote two posts about how to break a trigger midstream

Trigger in MySQL to prevent insertion (Apr 25, 2011)
check constraint does not work? (Dec 23, 2011)

Try this:

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `test`
BEFORE UPDATE ON `apply` 
FOR EACH ROW
BEGIN
    DECLARE dummy INT;

    IF NEW.cname = "hi" THEN          
        SET NEW.cname = "hello";
    ELSE
        SELECT no_such_column INTO dummy
        FROM information_schema.no_such_table;
    END IF;

END; $$

MySQL – How to Call Stored Procedure Inside Trigger

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

DELIMITER ;
DROP FUNCTION IF EXISTS to_updatequantity;

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

    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';
    ELSE 
        SET @msg = 'prod_qty is less than or equal to ordered qty'; 
    END IF;  

RETURN @msg;
END //
DELIMITER ;

Best Answer

Related Solutions

MySQL using trigger instead of check

MySQL – How to Call Stored Procedure Inside Trigger

Related Question