Hi, You might make some changes to your trigger to make it a bit more efficient...

I also recognize that you're not doing this as an Insert trigger.  I'd think that you want to handle the insert case as well, however perhaps you don't allow records to be created in the state that you're trying to count.  So, I'll skip that.    Likewise the Delete case.

You're also missing the situation where a case is edited so that it points to a different Service Agreement than it used to.

So, the trigger:

trigger UpdateCountOfScriptBuildsInProgress on Case (after update) {
    // Service Agreements can have multiple cases under them.  The goal is, when a Case is updated, we want to make 
    // sure that the Service Agreement object has an up to date count of the number of cases in a specific state.
    // Note that since we're not changing the Case object, and we want to base our results on the new values, we do this as an After trigger.
    
    // So, first we want to get all the service agreements that might be effected by changes to these cases.
    // Note: If other changes are made to the case, we don't care.
    Set<Id> serviceAgreementIds = new Set<Id>();
    for (Case c: trigger.new) {
        // Let's only deal with cases where the Reason or Status has changed, or the case is reparented
        if (
            (c.Reason != trigger.oldmap.get(c.id).Reason) ||
            (c.Status != trigger.oldmap.get(c.id).Status) ||
            (c.Service_Agreement__c != trigger.oldmap.get(c.id).Service_Agreement__c)
        ) {
            serviceAgreementIds.add(c.Service_Agreement__c);
            // If it's because it was reparented, we want to recompute the counts for BOTH Service_Agreements.
            if (c.Service_Agreement__c != trigger.oldmap.get(c.id).Service_Agreement__c) 
                serviceAgreementIds.add(trigger.oldmap.get(c.id).Service_Agreement__c);
        }
    }
    if (serviceAgreementIds.isEmpty()) return;

    // Ok, we have some Service Agreements which will have a changed count, let's get them, and all their relevant cases.
    // And for each one, if the count has changed, set the Count correctly.
   
    List<Service_Agreement__c> sas = new List<Service_Agreement__c>();
    for (Service_Agreement__c sa :[
        select id, COUNT_of_Script_Builds_In_Progress__c, 
            (select id from Cases__r where Reason = 'New Build'and Status = 'In Progress') 
        from Service_Agreement__c 
        where id in :serviceAgreementIds]) {
      
        // Only issue the update if the count has changed.
        if (sa.COUNT_of_Script_Builds_In_Progress__c != sa.Cases__r.size()) {
            sa.COUNT_of_Script_Builds_In_Progesss__c = sa.Cases__r.size();
            sas.add(sa);
        }
     }
     if (sas.isEmpty()) return;
     update sas;
}

And, for testing:

 
    

@isTest
private class testCountScriptBuilds {
// Helper to put the query in one place.
private static Integer getCount(Service_Agreement__c sa) {
    return [select COUNT_of_Script_Builds_In_Progress__c from Service_Agreement__c where id=:sa.id].COUNT_of_Script_Builds_In_Progress__c;
}

static testMethod void testCounts() {
    Service_Agreement__c sa1 = new Service_Agreement__c(name='sa1');  // other fields...
    Service_Agreement__c sa2 = new Service_Agreement__c(name='sa2');
    insert sa1;
    insert sa2;

    Case c1 = new Case(Service_Agreement__c = sa1.id, Status = 'New', Origin = 'Phone', Reason = 'Other');
    insert c1;

    // Now let's walk through some changes to c1;

    // First, let's make sure the count is correct to start
    system.assertEquals(0, getCount(sa1));

    // Now change it, but it shouldn't effect the count.
    c1.Status = 'Working';
    update c1;
    system.assertEquals(0, getCount(sa1));

    // Now change it, but it still shouldn't effect the count
    c1.Status = 'In Progress';
    update c1;
    system.assertEquals(0, getCount(sa1));

    // Now it should;
    c1.Reason = 'New Build';
    update c1;
    system.assertEquals(1, getCount(sa1));

    // Unchanged
    c1.Origin = 'Web';
    update c1;
    system.assertEquals(1, getCount(sa1));

    // Let's make sure the count for the second Service agreement is zero.
    system.assertEquals(0, getCount(sa2));
   
    // And let's do something sneaky... let's change the case so that it's now belongs to a different 
    // service agreement
    c1.Service_Agreement__c = sa2.id;
    update c1;
    // Now the count for the first should be zero, and the one for the second should be one.
    system.assertEquals(0, getCount(sa1));
    system.assertEquals(1, getCount(sa2));   

    // Change it back
    c1.Status = 'Closed';
    update c1;
    system.assertEquals(0, getCount(sa2));    
    

    // Now, you should do some similar testing for Bulk records you've created...
    // setting some of them to various status, making sure the count is correct, and
    // then updating some number of them to the status that matter, etc.
    // But, I'll leave that to you.

    //List<Case> cases = new List<Case>();
    //for (Integer i=0; i<200; i++) {
    //    cases.add(new Case(Service_Agreement__c = sa2.id, Status = 'New', Origin = 'Phone', Reason = 'Other'));
    //}
    //insert cases;
}

Hope it helps, best, Steve.

p.s. Note: I just typed this in, I'm sure there are some problems with it.  :-)   S.