Added = yes
I would use triggers on the
items table to update the
PR table. If the
Added field can be set to
yes after the record is inserted into the
items table, then I would only use an
after update and
after delete triggers , otherwise, I would use an
after insert as well.
I'll show one of the triggers, you can create the other ones based on this example. I would also consider moving the logic to a stored procedure that can be called by the triggers, given that the logic will be pretty much the same.
The code below counts the number of records in the items table with the same TransNo and counts the records for the same where Added is set to
yes, and assigns these numbers to 2 variables (`select ... into ... statement).
Then in an
if statement the status text is decided based on the logic you described.
Finally, the status field in the PR table is updated - assuming that this record already exists. You need to extend the code if this record in the PR table may not exist.
delimiter // create trigger items_added_after_update after update on items for each row begin declare all_count int; declare yes_count int; declare status_text varchar(20); select count(TransNo), count(if(added='Yes',1,null)) into all_count, yes_count from items where TransNo=NEW.TransNo; if all_count=yes_count then set status_text='Fully Selected'; elseif yes_count=0 then set status_text='Reviewed'; else set status_text='Partially Selected'; end if; update PR set PR.status=status_text where PR.TransNo=NEW.TransNo; end // delimiter ;