Automatic Field Update/ Calculated Field

I've been trying to find out if the following scenario is possible, I'll do my best to describe!

Table - trespondent

id, company, staff, responses


Table - tresults

id, q1, q2, q3, q4, q5

Ideally, what I'm looking to do is automatically UPDATE the responses field in trespondent each time an id is entered into the tresults table that matches the id in trespondent.

Effectively, th开发者_运维问答e responses field gives a running COUNT of the number of responses for that id in tresults.

Hope that makes sense?

it should be possible by using triggers

here is the manual: http://dev.mysql.com/doc/refman/5.1/de/create-trigger.html

and here is a example and more explanation: http://forge.mysql.com/wiki/Triggers

How about, when you actually need the number of responses, do:

SELECT count(responses)
FROM trespondent trp
JOIN tresults trs USING(id)
WHERE trp.id = some-id

Either perform all inserts (and deletes) into tresults via stored procedures that implement the logic you've described, or read up on triggers in MySQL, and place a trigger on the tresults table that 'does the magic'