Trigger to populate aggregation table

[Geoff Mina]

Hello,
I have a question about using a trigger to keep an aggregation table up-to-date. Lets say I have a couple of tables:

CREATE TABLE calls(
call_id INT NOT NULL,
call_result VARCHAR(40)
)

CREATE TABLE aggregate_data(
call_result VARCHAR(40),
call_count INT NOT NULL DEFAULT 0
)

What I would like to do is create a trigger that would fire AFTER UPDATE on the "calls" table. Based on the "call_result" column it would increment the call_count on the aggregate_data table. This is all pretty straight forward, except I am trying to do this in a highly concurrent, high simultaneous thread-count situation. I could potentially have thousands of updates per second, and I need to be sure when i do my call_count=call_count + 1 statement that it is synchronized across all threads. This is what I have so far:


CREATE TRIGGER agg_trigger AFTER UPDATE ON calls
FOR EACH ROW BEGIN
UPDATE aggregate_data SET call_count = call_count + 1
WHERE call_result = NEW.call_result;
END;

I know I am missing something here to make this thread safe, I am just not sure what it is.

Thanks for your help in advance.
-Geoff