How to bulkify a cross object update trigger

Question

I have a complicated trigger that I am trying to bulkify but I don't know how to go about it. We have two main objects, Prescriptions and Study Participants. When a new Prescription is added through Informatica we have only a few pieces of information to find the right Study Participant to attach it to. We have a Participant Code (created from the first digit of first name and last digit of last name plus the day, month and year of birth) and we have a Study. Each participant can be in multiple studies so the trigger has to find the right study to attach to.

We also have an issue with the Study being wrong on some of the Prescriptions so we have to look at the fill date compared to the date enrolled for those studies.

Here is my code. It works but it hits governor limits. Any suggestions appreciated.

    trigger Attach_Prescriptions on Prescription__c (before insert, before update) 
{

    for(Prescription__c a :Trigger.New) {





    List <Study_Participant__c> TREAT2 = [select id,Name,Participant__c,Study__c, Provider__c,Study_Provider__c, Date_Enrolled__c from Study_Participant__c where Name =:a.Participant_code__c and Study__c =: 'a00A000000dMIvSIAW' and  Date_Enrolled__c <:a.Fill_Date__c  ];             
      List <Study_Participant__c> TREAT = [select id,Name,Participant__c,Study__c,Provider__c,Study_Provider__c, Date_Enrolled__c from Study_Participant__c where Name =:a.Participant_code__c and Study__c =: 'a00A000000cRWxXIAW' ];   
       List <Study_Participant__c> Named = [select id,Name,Participant__c,Study__c,Provider__c,Study_Provider__c, Date_Enrolled__c from Study_Participant__c where Name =:a.Participant_code__c and Study__c =:a.study__c and Study__c !=:'a00A000000cRWxXIAW' and Study__c!=:'a00A000000dMIvSIAW'];  
            for (Study_Participant__c d: TREAT2)

            if  (a.Study__c =='a00A000000cRWxXIAW' && TREAT2.size()>0 ) {

               a.Patient__c=TREAT2.get(0).Participant__c;
                a.Provider__c=TREAT2.get(0).Provider__c;
                a.Study_Provider__c=TREAT2.get(0).Study_Provider__c;
                a.Study_Participant__c=TREAT2.get(0).id;}

     If(a.Study__c =='a00A000000cRWxXIAW' && TREAT2.isEmpty() && TREAT.size()>0){ 

              a.Patient__c=TREAT.get(0).Participant__c;
              a.Provider__c=TREAT.get(0).Provider__c;
              a.Study_Provider__c=TREAT.get(0).Study_Provider__c;
         a.Study_Participant__c=TREAT.get(0).id;
     }

      If(a.Study__c !='a00A000000cRWxXIAW' && Named.size()>0)// && a.Participant_code__c==d.Name && a.Study__c ==d.Study__c){
               {  
                a.Patient__c=Named.get(0).Participant__c;
                a.Provider__c=Named.get(0).Provider__c;
                a.Study_Provider__c=Named.get(0).Study_Provider__c;
                a.Study_Participant__c= Named.get(0).id;
               }   

     else if (TREAT.isEmpty() && TREAT2.isEmpty() && Named.isEmpty())
            {
                a.Study_Provider__c = 'a05A000000Jqa3s';
                a.Study_Participant__c='a02A000000DbIFn'; 
            }

               }}

Answer 1

I spent way more time on this then I should have. Granted I do not know your org so this is the best I can do with what you provided. Also a bunch of your logic was off so I did what I could to simplify it.

You need to look at my comments on the query and fill in any other parts I made assumptions on.

trigger Attach_Prescriptions on Prescription__c (before insert, before update) 
{
map<String, list<Study_Participant__c>) LinkMap= new map<String, list<Study_Participant__c>){}; // Map that will serve as lookup
Study_Participant__c[] TreatmentsQ = [select id, name, study__c... from Study_Participant__c where ...]; // Get all the Study Participants -- You'll want to find a way to reduce this list with a where clause

//this will create the map to find a list of Study_Participant__c's
    for(Study_Participant__c s: TreatmentsQ){
     if(LinkMap.get(s.Name) == null){
       LinkMap.put(s.name, new list<Study_Participant__c>());
      }
      LinkMap.get(s.name).add(s);
    }

        for(Prescription__c a: Trigger.New){
        list<Study_Participant__c> temp = new list<Study_Participant__c>(LinkMap.get(a.Participant_code__c )); // this is the same as saying if Name = Participant_code__c now you have a list of Study_Participant__c's that have that Participant_code__c
                        if(temp.size() > 0){ //if they were in a study

                            for(Study_Participant__c sp: temp){
                                if(sp.study__c == a.study__c && sp.Date_Enrolled__c  < a.fill_date__c){ 
                                    // if the Prescription__c.study__c == Study_Participant__c.study__c and  Prescription__c.Date_Enrolled__c < Study_Participant__c.fill_date__c date then fill that list value
                                    // IE if the Prescription__c.Study is "a00A000000dMIvSIAW" and the  Study_Participant__c.study__c is also "a00A000000dMIvSIAW"  it will check to make sure the dates are right same and fill in that info. 

                                   a.Patient__c=sp.Participant__c;
                                    a.Provider__c=sp.Provider__c;
                                    a.Study_Provider__c=sp.Study_Provider__c;
                                    a.Study_Participant__c= sp.id;
                                }
                             else if(sp.study__c == a study__c){
                               //Do same logic as above if the studies match but fill dates don't?
                               }
                            }


                        }
                        //there's no Name matching Participant_code__c
                        else{
                         a.Study_Provider__c = 'a05A000000Jqa3s';
                         a.Study_Participant__c='a02A000000DbIFn'; 
                        }
        }

}