Mysql Querys and functions

Question

This may be a stupid question but is it possible to store a count query in a field in a table such that when the result of the count changes the result will update itself?

Thread(ThreadID,NumMessages)  
Message(MessageID,ThreadID,MessageText,PreviousMessage)

I want to update the Thread.NumMessages field any time a message with the corresponding ThreadID gets added or removed. I know I can do this by incrementing/decrementing the Thread.NumMessages field of by using a count query

SELECT COUNT(*), FROM SCHEMA.Message WHERE ThreadID='SOMETHREADID'

But is there anyway of setting up the NumMessages field so this is kept up to date without it being done explicitly at every addition and delete?

Thanks Graeme

Answer 1

yes, you can use a view as the implementation of your Thread table.

http://dev.mysql.com/doc/refman/5.1/en/create-view.html

Create view thread_view 
    select 
        count(*) as NumMessages, 
        threadID 
        from message 
        group by threadID

Answer 2

Triggers!

You're wanting a trigger. This should help you with your searching (knowing what to look for is half the battle). http://www.databasedesign-resource.com/mysql-triggers.html

Basically, a trigger fires any time a designated event (such as insert) on a table is tripped, and executes a stored procedure.

your stored procedure would then check to make sure it's something it cares about (you can add all sorts of logic here), then does something (increment that value?) before MySQL executes the original query that triggered it in the first place.

http://www.roseindia.net/mysql/mysql5/triggers.shtml