As I couldn't find anything like this anywhere I'm guessing it's unlikely that it is possible. Just to be sure I figured I'd ask it here.
I have a database with 2 tables. Lets name them table A and B for now. I have a bunch of items in table B, of which each item has a many to one relation to an item in table A. Now in table A I have a field which should contain the count of how many items in table B relate to that particular item in A. Example table A:
+----+------+---------+
| id | data | count_b |
+----+------+---------+
| 1 | blah | 2 |
| 2 | blah | 0 |
| 3 | blah | 1 |
+----+------+---------+
Example table B:
+----+------+--------+
| id | data | parent |
+----+------+--------+
| 1 | blah | 1 |
| 2 | blah | 1 |
| 3 | blah | 3 |
+----+------+--------+
Now I am wondering if there is a way to automatically update the column count_b in table A everytime a row is inserted in to B?
I'd like to achieve this using mysql only. My web application uses doctrine for database mappings, so if I could somehow define it as a relation there, it would be fine as well.
I tried explaining my question as clear as possible, if it's unclear just le me know in the comments. Thanks in advance.
To do exactly what you want, you can create a TRIGGER. To achieve a somewhat similar result, you could make a view on this table that contains an extra column containing this count.
I have a strong preference for the second option, because it prevents inserting redundant data into your database and also eliminates the need for a trigger, which is good if you realize that triggers can quickly turn into a performance and maintenance nightmare.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments