I have these two tables:

mysql> select * from orders;
| Customer_ID | Item    |
| A01         | CPU     |
| A01         | Monitor |
| A02         | Monitor |
| A03         | UPS     |
| A02         | UPS     |
| A03         | CPU     |
6 rows in set (0.00 sec)
mysql> select * from ordered_items;
| Item    | Qty |
| CPU     |   2 |
| Monitor |   2 |
| UPS     |   2 |
3 rows in set (0.00 sec)

I need to count each items in the orders table and update the Qty of the ordered_items table whenever the orders table is updated.

Is it possible to do with a trigger?


Yes, you could do this via a Trigger. But you would need 3 triggers: 1 on insert, 1 on delete, 1 on update (which would do nothing, unless you modify the Item column). This would be harder to mantain, and would slow down operations in orders table.

Another option is using a view:

CREATE OR REPLACE VIEW `ordered_items`
        SELECT `Item`, COUNT(*) AS `Qty`
            FROM `orders`
            GROUP BY `Item`;

The drawback is that reading ordered_items is slower, because the GROUP BY on orders is executed each time. If this is a problem, you could periodically copy data from ordered_items view into a physical table. This could be done nightly via an Event, if you have MANY data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.