Before Insert Trigger - Query Only Related Records To Incoming Record

Question

Ideally this trigger would look to see if a record of a certain record type has a yes flag and set it to no before inserting a new record (all under the same related Contact_c). More specifically, I would like to take the value of Contact_c from the incoming record and query all MyActivity__c records in Salesforce to gather a list that match (including the other query parameters - flag/record type/etc.).

This trigger I've written seems to gather all MyActivity__c records for every Contact in the database and then iterates through them, setting the flag of their records to NO. Hoping someone can point out where I'm going wrong. Thank you.

Trigger:

trigger MyTrigger on MyActivity__c (before insert) {

    List<MyActivity__c> contactrecords = new List<MyActivity__c>();
    ID rtId = [SELECT Id FROM RecordType WHERE Name = 'Record Type Name'].Id;

    for(MyActivity__c r: trigger.new){

     if(r.RecordTypeId == rtId){

        String c = r.Contact__c;
        String i = r.Id;

        List<MyActivity__c> act = [SELECT Contact__c FROM MyActivity__c WHERE Contact__c = :c AND Id != :i AND Flag__c = 'Yes' AND RecordTypeId IN (SELECT Id FROM RecordType WHERE Name = 'Record Type Name')];
        for (MyActivity__c contactlist : act){         
            contactlist.Flag__c = 'No';
            contactrecords.add(contactlist);
        }
     }
    }

    if(contactrecords.size()>0) {
        update contactrecords;
    }   
}

Answer 1

What exactly is your requirement? it is a little hazy to understand. As far as I can tell you want to do the following.

1. Check the field Flag_c when an MyActivity_c record is inserted where the recordtypeID on the inserted record is "Record Type Name" and set it to No if its value is yes
2. Gather a list of existing MyActivity_c records where the recordtype is "Record Type Name" and the field "Contact_c" matches the original inserted record.

A couple of questions

1. Is Flag__c a Checkbox? maybe consider if it should be (true,false) rather than "Yes" / "No".
2. Why are we checking if the field is Yes before setting to No, if the field is No already there is no difference.
3. What do you want to do with the list of existing MyActivity__c records once you have them?

That being said there are a number of issues with this trigger, starting with the fact that you are executing a SOQL query within a for loop which is against best practice. Always try to execute queries outside of any loop structure.

Also when you query a Recordtype you should also try to query the objecttype not just the name and preferably the developer name (Usually the same as name with underscores, but the name may change, hence why its good to query developername)

Here is an example of how you might write this trigger based on what I can gather from your requirements, with comments explaining step by step.

trigger MyTrigger on MyActivity__c (before insert)
{

    // a map to store your Contact__c to related MyActivity__c records
    map<Id,list<myActivity__c>>     mapIdtoActivities   = new map<Id, list<myActivity>>();

    // OR you just get one big old list depending on what you want to do with them
    list<myActivity__c>             listOtherActivities = new list<myActivity>();

    // Query the correct recordtype
    ID rtId = [SELECT Id FROM RecordType WHERE sobjectType='MyActivity__c' AND DeveloperName = 'Record Type Name'].Id;

    for(MyActivity__c r: trigger.new)
    {
        // For the activity records in the trigger context that have this recordtype
        if(r.RecordTypeId == rtId)
        {
            // set the flag to no
            r.Flag__c == 'No'; // Or false if you change to checkbox

            // Create a key value for each Contact__c
            mapIdtoActivities.put(r.Contact__c. null);
        }
    }

    // Query the other activity records related to the ones in your trigger (map keys & recordtype & flag = no)
    for(MyActivity__c OtherActivityRecord : [   SELECT Id, Contact__c FROM MyActivity__c
                                                WHERE RecordTypeId = : rtId AND Flag__c = 'No'
                                                AND Contact__c IN : mapIdtoActivities.keyset()])
    {
        // NOW depending on what you want to do with them you can either create multiple lists per contact i.e.

            // Check if we have already created a list for this contact__c record
            if(mapIdtoActivities.get(r.Contact__c) == null)
            {
                // There is no list of Activities so create one and add the first activity record to it
                mapIdtoActivities.put(r.Contact__c, new list<MyActivity__c>{OtherActivityRecord});
            }
            else    
            {
                // There is a list of Activities so add to it
                mapIdtoActivities.get(r.Contact__c).add(OtherActivityRecord);
            }

         // OR you can just dump them all in one big list

         listOtherActivities.add(OtherActivityRecord);
    }

    // Do something with your shiny new lists here.


    // no need to query the size anymore so I am told
    //if(contactrecords.size()>0)
    //{
        update listOtherActivities;
    //}   
}