Say i have a table called trx_request with the following data

id | confirmed(TIME) | finished(TIME)
1  | 12:05:00        | NULL
2  | 13:11:00        | NULL

and another table called trx_updates with the following data

request(FK) | role(INT) | added(TIME)
1           | 1         | 14:02:00

I'd like to make a trigger that after a record is inserted into trx_updates, counts how many records has the same value in the request field (such as how many '1's there are). And then, does a check to see if the count reaches 4 ('1' shows up 4 times). When it does, it updates the trx_requests.finished to NOW().

How would one make a count statement in triggers? or are triggers the way to go?

1 Answer 1


This trigger does the job:

create trigger trx_updates_atrig
after insert on trx_updates for each row begin
DECLARE updatecount INT;
  set updatecount = ( select count(*) from trx_updates where request = new.request );
  if updatecount=4
      update trx_request set finished=NOW() where id = new.request;
  end if;

SQLFiddle Demo.

  • Thanks a lot @Phil, I can clearly understand your answer, but somehow I keep getting SQL Error (1109): Unknown table 'trx_request' in field list right after I insert the fourth record. Do you have any idea what I might be missing?
    – endy.hardy
    Commented Jan 29, 2014 at 1:30
  • Is trx_request the correct table name to update? Is it in the same database (if not, it will need fully qualifying with the DB name)? Do you have permission to update it?
    – Philᵀᴹ
    Commented Jan 29, 2014 at 1:51
  • Oh, I see it is called trx_request in one part of the question and trx_requests in another. That is probably the problem. Learn to troubleshoot? :P
    – Philᵀᴹ
    Commented Jan 29, 2014 at 1:52
  • Oh my, that IS the problem. My bad :) Thanks heaps
    – endy.hardy
    Commented Jan 29, 2014 at 2:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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