5

I have made a little trigger that updates record owner based on a parent(and another parent) record owner.

The data model is: HR_Data__c < Clock_in_out_user__c < Clock_in_out_Date__c.

The problem is that when user is not in the office, the colleague will clock him in with record type Unplanned Outage. However, the record owner will be of colleague and that screws up the reports a bit.

Here's the code:

trigger changeUnauthorizedClockinOwner on Clock_in_out_Date__c (before insert, before update) {
    // Get the record type id based on name Unplanned Outage
RecordType unplannedOutage = [select Id from RecordType where Name = 'Unplanned Outage' and SobjectType = 'Clock_in_out_Date__c' limit 1];
    // Get the owner id from the related clock out user and hr data objects
Clock_in_out_Date__c newOwner = [ 
Select id, Clock_in_out_User__r.HR_Data_User__r.OwnerId 
        From Clock_in_out_Date__c 
           where id in : trigger.new];   

        // Update the owner when it matches the Unplanned Outage ID   
    for(Clock_in_out_Date__c newRecord: trigger.new) {
        if (newRecord.RecordTypeId==unplannedOutage.id){
            newRecord.OwnerId=newOwner.Clock_in_out_User__r.HR_Data_User__r.OwnerId;
            }
    }}

And the test class (a bit long winded):

@isTest(SeeAllData=true)
private class TestChangeUnauthorizedClockinOwner{
    Static testMethod void TESTOwnerChange(){

        // Insert test user
        Profile p = [select id from profile where name='Standard User'];
        User testUser = new User(alias = 'test123', email='test123990@noemail.com',
            emailencodingkey='UTF-8', firstname = 'Wesley', lastname='Testing', languagelocalekey='en_US',
            localesidkey='en_US', profileid = p.Id, country='United States',
            timezonesidkey='America/Los_Angeles', username='test123990@noemail.com');
        insert testUser;

        // Insert HR Data test record
        HR_Data__c TestHRData = new HR_Data__c(
            name = 'Test HR Data User',
            ownerId = testUser.id);
        insert TestHRData;

        // Insert linked HR Data User            
        Clock_in_out_User__c TestClockInOutUser = new Clock_in_out_User__c(
            Name = 'Test Clock User',
            Hidden_Check_last_enter_Date_not_same__c = System.Today()+410,
            HR_Data_User__c = TestHRData.Id);
        insert TestClockInOutUser;

        // Recordtype Unplanned Outage SOQL
        RecordType TestUnplannedOutage = [
            select Id from RecordType 
            where Name = 'Unplanned Outage' 
            and SobjectType = 'Clock_in_out_Date__c' 
            limit 1];

        // Clockin Time Date insert Test record
        Clock_in_out_Date__c TestClockDate = new Clock_in_out_Date__c(
            RecordTypeId=TestUnplannedOutage.id,
            Clock_in_out_User__c = TestClockInOutUser.Id,
            Clocking_in_date__c = system.Today()+3);

        insert TestClockDate; }}

The full test result is:

Error Message   System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, changeUnauthorizedClockinOwner: execution of BeforeInsert
caused by: System.QueryException: List has no rows for assignment to SObject
Trigger.changeUnauthorizedClockinOwner: line 10, column 1: []
Stack Trace Class.TestChangeUnauthorizedClockinOwner.TESTOwnerChange: line 39, column 1
18

Let's break down the error message you received:

System.DmlException: Insert failed. This is where the error was thrown. During all of the processing that happens behind the scenes of an insert (e.g. triggers, validation rules, workflows, etc), something went wrong.

First exception on row 0; If the failed insert included multiple rows to insert, this would tell you which row caused the first failure.

first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, This is a fairly catch-all error that just means an insert/update/upsert failed; keep reading for more info.

changeUnauthorizedClockinOwner: execution of BeforeInsert Now we are getting somewhere. The actual error occured in the trigger changeUnauthorizedClockinOwner during execution of a Before Insert call.

caused by: System.QueryException: List has no rows for assignment to SObject And here we are - the root cause. "List has no rows for assignment to SObject" happens when you assign a query to an SObject variable instead of a list variable, and the query returns no records.

A brief aside to discuss this error: Consider these three queries:

Account a1 = [Select id, name from Account where name='Acme, Inc.'];

Account a2 = [Select id, name from Account where name='Acme, Inc.' limit 1];

list<Account> accts = [Select id, name from Account where name='Acme, Inc.'];

The first query will only work (without throwing an exception) if there is EXACTLY 1 Account named "Acme, Inc." If there are 0 or 2+ accounts, the result can't be assigned to an SObject. The second query will handle the 2+ scenario, as the limit ensures only 1 record will be returned, but if there are 0 accounts, the above error will be thrown. The third query handles all of these cases; if there are 0 accounts, the result is an empty list; you can test (accts.size()==0) in your code. If you know you only want the first result, you can still use limit 1 and assign to the list variable. Just remember that limit 1 only limits the maximum results, not the minimum; it doesn't make it safe to blindly assign to a singular SObject variable.

As a rule, you should always assign a query result to a list and test the list. If your logic has a good reason to believe that exactly one record should exist (e.g., querying for a Profile), you should wrap the query in a try-catch block and gracefully handle any errors.

Back to breaking down the error:

Trigger.changeUnauthorizedClockinOwner: line 10, column 1: [] Here's where you tried to assign a query an SObject. The line number doesn't match up to your code above, which I assume you may have truncated or reformatted for this site. Take a look in the Developer Console or editor of your choice and see what query starts on line 10.

Stack Trace Class.TestChangeUnauthorizedClockinOwner.TESTOwnerChange: line 39, column 1 Finally, a list of the method calls leading to the error; in this case, it's just the code that invoked the trigger; Line 39 is sure to be an insert statement.

With the line numbers not matching up, it's a little hard to be sure, but I'm pretty sure that your trigger's query to assign newOwner is returning no results. Why? You are querying Clock_in_out_Date__c for the newly inserted record, but you are in the before trigger, so the record has not been inserted yet. You need to query the HR_Data_User__c table directly.

You also need to "bulkify" your trigger. Even if you expect these records to only be inserted one-at-a-time, the system can gather up several inserts and process them at once. See the online docs for more on bulkification. Here's your trigger, properly bulkified to update the owner via the HR_Data_User__r relationship. Note that this code assumes HR_Data_User__c will never be null on an inserted record (just as your code did).

trigger changeUnauthorizedClockinOwner on Clock_in_out_Date__c (before insert, before update) {
// Get the record type id based on name Unplanned Outage
    RecordType unplannedOutage = [select Id from RecordType where Name = 'Unplanned Outage' and SobjectType = 'Clock_in_out_Date__c' limit 1];

    //gather all of the HR_Data_User__c Ids for all of the records being inserted
    set<id> hrIds = new set<id>();
    for(Clock_in_out_Date__c newRecord: trigger.new) {
        hrIds.add(newRecord.HR_Data_User__c);
    }

    //look up the owners; building a map of HR_Data_User__c Ids to HR_Data_User__c objects
    map<id, HR_Data_User__c> userMap = new map<id, HR_Data_User__c>([select id, OwnerId from HR_Data_User__c where id in hrIds]);

    // Update the owner when it matches the Unplanned Outage ID          
    for(Clock_in_out_Date__c newRecord: trigger.new) {
        //if HR_Data_User__c is null, we can't lookup the correct user, so skip
        if (newRecord.HR_Data_User__c!=null) {
            if (newRecord.RecordTypeId==unplannedOutage.id){
                newRecord.OwnerId=userMap.get(newRecord.HR_Data_User__c).OwnerId;
            }
        }
    }
}
6
  • Hi Jason, thank you so much for this amazing answer. It does make so much sense. I went through the code and fixed couple of bits as object names weren't exactly the same as you typed. I do however encounter error on the last statement - newRecord.OwnerId=userMap.get(HR_Data_User__c).OwnerId, where I get "Variable HR_Data_User__c does not exist." Again thanks for your help, this is such a massive learning experience for me!
    – dzh
    Jan 27 '14 at 18:05
  • I did however tried to experiment with a list rather than map, and then assign as "=userMap[0].OwnerId" (doesn't this debulkifies it again?), which does throw an error. I found I should test whether it's empty (if(!userMap.isEmpty()){}), so I did and trigger does not work. Does this result suggest that I screwed up something above with the SOQL/relational logic?
    – dzh
    Jan 27 '14 at 18:09
  • @Dzh Glad this was helpful. I've updated the line of code that assigns newRecord.OwnerId to correct the bug. By using a map, we can look up the correct HR_Data_User__c record for each newRecord entry. If you use a list, you may get the wrong record. Because the map is being populated by a query based on the new records, you shouldn't need to check map.isEmpty(), unless newRecord.HR_Data_User__c can be null. However, we can avoid that issue as well by modifying the final for loop to ignore records with null NR_Data_User__c values - see code above for changes. Jan 27 '14 at 18:52
  • Hi Jason, thanks again for the help. By looking deeper there was an issue to relational logic, hence after modifying SOQL to map<id, Clock_in_out_User__c > userIds = new map<id, Clock_in_out_User__c >([select id, HR_Data_User__r.ownerid from Clock_in_out_User__c where id in : hrIds]); and assignment statement, I was able to successfully deploy. Very appreciated!
    – dzh
    Jan 28 '14 at 14:49
  • However, I was trying to solve this by creating map of HR_data_user__c ids on Clock_in_out_User__c, and then another map of HR_Data__C owner ids on HR_Data__c. My limited knowledge of maps failed me there, but I would be keen to know the correct expression. Here's what I tried: map<id, Clock_in_out_User__c > userIds = new map<id, Clock_in_out_User__c >([select id, HR_Data_User__c from Clock_in_out_User__c where id in : hrIds]); map<id, HR_Data__c> userMap = new map<id, HR_Data__c>([select id, OwnerId from HR_Data__c where id in : userIds.get(Clock_in_out_User__c).HR_Data_User__c]);
    – dzh
    Jan 28 '14 at 14:52
1

1) I'm not sure what you're trying to do, specially the limit 1 thing is a bit unorthodox and worry some to me. It doesn't look very bulkified but I'm unfamiliar with your data or process. If you want usefull help, it may be work trying to explain what you want to achieve with this trigger.

2) @isTest(SeeAllData=true) , are you sure you need that ? Using real data makes your results unpredictable and breakable. I believe you don't need to explicitly set this to query recordtypes.

 Clock_in_out_Date__c newOwner = [ 
                                  SELECT id, Clock_in_out_User__r.HR_Data_User__r.OwnerId 
                                  FROM Clock_in_out_Date__c 
                                  WHERE id in : trigger.new limit 1
                                  /*AND Clock_in_out_User__c!=null 
                                  ANDClock_in_out_User__r.HR_Data_User__c!=null */
                                  ]; 

3)If there is no Clock_in_out_Date__c data in your org/test data at all, or no related data for the record that's being inserted the above query will always result to 0 results. Resulting in the exception you have posted. This is not just in your test code btw. I think you may want to re-design your trigger.

4
  • Hi, thanks for comments. 1) The limit 1 thing was a legacy from the experiments. Code would test even without it. Updated the description. 2) Again, managed to actually deploy broken code and this was the only way to do it. 3) Can you explain more about this? As you see in the test class, I am inserting related data. Do you mean I should upload more data to sandbox and test it using the test class?
    – dzh
    Jan 24 '14 at 16:31
  • 3) -> it's not about the test class, I think the flaw is in your trigger itself. How can you be sure that query will have a result ? What data are you trying to get, based on what logical relation ? Jan 24 '14 at 16:49
  • Im trying to get owner of a HR_Data__c record. As they are related by lookup, rather than master-detail, I suppose there could be parentless records, hence what I could do then?
    – dzh
    Jan 24 '14 at 17:26
  • would you mind updating your question with a short description of the data model, and how the new owner can be identified through that model ? Jan 24 '14 at 17:50
1

User Jason Clark had an excellent description and lead me to solving the problem with this code:

   trigger changeUnauthorizedClockinOwner on Clock_in_out_Date__c (before insert, before update) {
// Get the record type id based on name Unplanned Outage
    RecordType unplannedOutage = [select Id from RecordType where Name = 'Unplanned Outage' and SobjectType = 'Clock_in_out_Date__c' limit 1];

    //gather all of the HR_Data_User__c Ids for all of the records being inserted
    set<id> hrIds = new set<id>();
    for(Clock_in_out_Date__c newRecord: trigger.new) {
        hrIds.add(newRecord.Clock_in_out_User__c);
    }
    // Gather owner Ids from a related HR_Data_c using related field HR_Data_user__c 
    map<id, Clock_in_out_User__c > userIds = new map<id, Clock_in_out_User__c >([select id, HR_Data_User__r.ownerid from Clock_in_out_User__c where id in : hrIds]);

    //look up the owners; building a map of HR_Data_User__c Ids to HR_Data_User__c objects

    // Update the owner when it matches the Unplanned Outage ID          
    for(Clock_in_out_Date__c newRecord: trigger.new) {
        //if HR_Data_User__c is null, we can't lookup the correct user, so skip
        if (newRecord.Clock_in_out_user__c!=null) {
            if (newRecord.RecordTypeId==unplannedOutage.id){
                newRecord.OwnerId=userIds.get(newrecord.Clock_in_out_user__c).HR_Data_User__r.OwnerId;
            }
        }
    }
}

As Jason mentioned, the issue is that query might return an empty result on which the test would fail.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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