Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Thanks in advance.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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
    then
      update trx_request set finished=NOW() where id = new.request;
  end if;
end//

SQLFiddle Demo.

share|improve this answer
    
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 Jan 29 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 Jan 29 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 Jan 29 at 1:52
    
Oh my, that IS the problem. My bad :) Thanks heaps –  endy.hardy Jan 29 at 2:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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