Results 1 to 1 of 1
\r\n \r\n\r\n \r\n \r\n
\r\n
\r\n
\r\n I am trying to create a DB that lists the calories for individual groceries as well as for items that are composed of two or more groceries. So far I have created two tables and one trigger. The trigger recalculates the calories of composed items every time the amounts are updated.
\r\n
\r\nHow do I take care of changes in the kcal column? Is it possible to create a second trigger, that triggers the update_kcal trigger?
\r\n
\r\n
\r\n
Code:
\r\n
select * from grocery;\r\n+-' + '-' + '-' + '-+-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-+-' + '-' + '-' + '-' + '-' + '-+\r\n| id | name             | kcal |\r\n+-' + '-' + '-' + '-+-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-+-' + '-' + '-' + '-' + '-' + '-+\r\n|  1 | Black Coffee     |    0 |\r\n|  2 | Sugar            |  387 |\r\n|  3 | Milk             |   42 |\r\n|  4 | My Custom Coffee | NULL |\r\n+-' + '-' + '-' + '-+-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-+-' + '-' + '-' + '-' + '-' + '-+
\r\n
\r\n
Code:
\r\n
select * from grocery_composition;\r\n+-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-+-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-+-' + '-' + '-' + '-' + '-' + '-' + '-' + '-+\r\n| fk_grocery | fk_ingredient | amount |\r\n+-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-+-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-+-' + '-' + '-' + '-' + '-' + '-' + '-' + '-+\r\n|          4 |             1 |   80.0 |\r\n|          4 |             2 |   15.0 |\r\n|          4 |             3 |    5.0 |\r\n+-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-+-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-' + '-+-' + '-' + '-' + '-' + '-' + '-' + '-' + '-+
\r\n
The foreign keys are setup as this:
\r\n
\r\n
\r\n
Code:
\r\n
grocery_composition.fk_grocery -> grocery.id\r\ngrocery_composition.fk_ingredient -> grocery.id
\r\n

\r\n
\r\n
Code:
\r\n
CREATE TRIGGER `update_kcal` AFTER UPDATE ON `grocery_composition` FOR EACH ROW\r\nUPDATE\r\n  `grocery`,\r\n  (\r\n    SELECT\r\n      SUM(grocery.kcal / 100 * grocery_composition.amount) as kcal,\r\n    FROM\r\n      `grocery_composition`\r\n    JOIN\r\n      `grocery` ON `grocery`.`id` = `grocery_composition`.`fk_ingredient`\r\n    WHERE\r\n      `grocery_composition`.`fk_grocery` = NEW.`fk_grocery`\r\n  ) AS `total`\r\nSET\r\n  `grocery`.`energy` = `total`.`kcal`,\r\nWHERE\r\n  `id` = NEW.`fk_grocery`
\r\n
\r\n
\r\n
\r\n\r\n \r\n\r\n\r\n
\r\n \r\n \r\n \r\n \r\n \r\n
\r\n
\r\n
\r\n
\r\n \r\n \r\n \r\n \r\n \r\n Reply With Quote Reply With Quote \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n
\r\n
\r\n
\r\n\r\n'; // next/previous post info pn[6646440] = "6646440,6646440"; pn[0] = ",6646440"; // cached usernames pu[0] = guestphrase; pu[365409] = "Marcel-1"; // -->

Threaded View

  1. #1
    Join Date
    Jan 2017
    Posts
    1

    Unanswered: Recalculate values in hierarchy

    I am trying to create a DB that lists the calories for individual groceries as well as for items that are composed of two or more groceries. So far I have created two tables and one trigger. The trigger recalculates the calories of composed items every time the amounts are updated.

    How do I take care of changes in the kcal column? Is it possible to create a second trigger, that triggers the update_kcal trigger?

    Code:
    select * from grocery;
    +----+------------------+------+
    | id | name             | kcal |
    +----+------------------+------+
    |  1 | Black Coffee     |    0 |
    |  2 | Sugar            |  387 |
    |  3 | Milk             |   42 |
    |  4 | My Custom Coffee | NULL |
    +----+------------------+------+
    Code:
    select * from grocery_composition;
    +------------+---------------+--------+
    | fk_grocery | fk_ingredient | amount |
    +------------+---------------+--------+
    |          4 |             1 |   80.0 |
    |          4 |             2 |   15.0 |
    |          4 |             3 |    5.0 |
    +------------+---------------+--------+
    The foreign keys are setup as this:

    Code:
    grocery_composition.fk_grocery -> grocery.id
    grocery_composition.fk_ingredient -> grocery.id

    Code:
    CREATE TRIGGER `update_kcal` AFTER UPDATE ON `grocery_composition` FOR EACH ROW
    UPDATE
      `grocery`,
      (
        SELECT
          SUM(grocery.kcal / 100 * grocery_composition.amount) as kcal,
        FROM
          `grocery_composition`
        JOIN
          `grocery` ON `grocery`.`id` = `grocery_composition`.`fk_ingredient`
        WHERE
          `grocery_composition`.`fk_grocery` = NEW.`fk_grocery`
      ) AS `total`
    SET
      `grocery`.`energy` = `total`.`kcal`,
    WHERE
      `id` = NEW.`fk_grocery`

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •