Test Class for a trigger failure: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY

Question

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='[email protected]',
            emailencodingkey='UTF-8', firstname = 'Wesley', lastname='Testing', languagelocalekey='en_US',
            localesidkey='en_US', profileid = p.Id, country='United States',
            timezonesidkey='America/Los_Angeles', username='[email protected]');
        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

Answer 1

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;
            }
        }
    }
}

Answer 2

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.

Answer 3

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.