title: Trigger that validates line item amounts when updating a table in MySql
body: <p>I am working on a lab for school which states: "Create a trigger named products_before_update that checks the new value for the discount_percent column of the Products table. This trigger should raise an appropriate error if the discount percent is greater than 100 or less than 0.</p>
  
  <p>If the new discount percent is between 0 and 1, this trigger should modify the new discount percent by multiplying it by 100. That way, a discount percent of .2 becomes 20.</p>
  
  <p>Test this trigger with an appropriate UPDATE statement."</p>
  
  <p>My script updates the discount percent column successfully, however the "If, Then" portion does not seem to be accomplishing anything. IF I input a discount percent over 100, it does not show any errors and simply updates it. Same issue if my discount percent is less than zero. Also, if I input a number from 0-1 then it is not multiplying it by 10. Any advise would be greatly appreciated!</p>
  
  <p>Here is my code:</p>
  
  <pre><code>USE my_guitar_shop;
  
  DROP TRIGGER IF EXISTS products_before_update;
  
  DELIMITER //
  
  CREATE TRIGGER products_before_update
     BEFORE UPDATE ON Products
     FOR EACH ROW
  BEGIN
  DECLARE discount_percent_amount INT;
  
  SELECT discount_percent
  INTO discount_percent_amount
  FROM Products
  WHERE product_id = NEW.product_id;
  
  IF discount_percent_amount &gt; 100 THEN
  SIGNAL SQLSTATE 'HY000'
      set message_text = 
          'the discount percent cannot be greater than 100';
  ELSEIF discount_percent_amount &lt; 0 THEN
  SIGNAL SQLSTATE 'HY000'
      set message_text = 
          'the discount percent cannot be less than 0';
  ELSEIF discount_percent_amount &lt; 1 THEN
  SET discount_percent_amount = (discount_percent * 10);
  END IF;
  
  END//
  
  DELIMITER ;
  
  UPDATE Products
  SET discount_percent = .4
  WHERE product_id = 3;
  
  SELECT * FROM products;
  </code></pre>