Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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