Trigger for updating a count of rows from a table into another table

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`
    AS
        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.

StackExchange Database Administrators Q#42966, answer score: 2