Question 283700 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Reduce price by 10% using a trigger

I’m trying to create a trigger where the price entered in the Price column of Courses table would automatically be reduced by 10% after entry. I’m brand new to triggers and am creating my first one; I’m not sure if MySQL lets you declare empty variables which is what I’m assuming I’ll need to subtract it by the original value entered

Delimiter #
CREATE TRIGGER course_discount_table
BEFORE UPDATE ON Courses 
FOR EACH ROW 
SET New.Price = (New 0.10 * Price)
SET Price = (Price - Price)
END #
DELIMITER ;

Of course, the code is a total mess but would really appreciate any help

Tags:

2

Answers


  1. 0

    the following would every time you update a course take effect and the proce reduced.

    Better would be to have a column, that control that behaviour, or a lowest price that would not be underwhelmed.

    Delimiter #
CREATE TRIGGER course_discount_table
BEFORE UPDATE ON Courses 
FOR EACH ROW 
   SET New.Price = NEW.Price - ( 0.10 * NEW.Price);
END #
DELIMITER ;
    Login or Signup to reply.
  2. 0

    Several tips:

    • If you have an END, you must also have a BEGIN.

    • If your trigger body were only one statement, you don’t need a BEGIN...END block, and in that case you wouldn’t need to change the DELIMITER.

    • Every time you reference a column of the new row, prefix it with NEW..

    • Each statement in a block must end with a semicolon.

    • I have no idea what SET PRICE = (Price - Price) is even supposed to do! 🙂

    • Changing the price in a BEFORE UPDATE trigger means it will continue to reduce by 10% after every UPDATE. So if you change the title or the time or whatever, it’ll keep reducing until it’s zero.

      It would be better, in my opinion, to record the original price, then
      add a generated column to show the discounted price. This you can do without a trigger.

    Demo:

    mysql> CREATE TABLE Courses (
    ->   id SERIAL PRIMARY KEY,
    ->   name TEXT NOT NULL,
    ->   original_price NUMERIC(9,2) NOT NULL,
    ->   discounted_price NUMERIC(9,2) AS (original_price * 0.90)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into Courses set name = 'Math', original_price = 100;
Query OK, 1 row affected (0.00 sec)

mysql> select * from Courses;
+----+------+----------------+------------------+
| id | name | original_price | discounted_price |
+----+------+----------------+------------------+
|  1 | Math |         100.00 |            90.00 |
+----+------+----------------+------------------+
1 row in set (0.00 sec)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search