I'm trying to create a trigger that will remove a custom object called customer when another customer with the same first name, last name and title (MR, MRS, Miss) is added, so only the second entry is stored while the first entry is deleted. This all works well but Customer__c also holds a Lookup to Account__c. I want to check a custom value in the old entry and check it against the same field of the latest entry and see if it is higher. If so, the lookup to account should be changed to the lookup in the first entry. Maybe my code makes more clear:
trigger handleDuplicates on Customer__c( before insert, before update )
{
//run through all the customers that are addressed at any one time
for ( Customer__c c : Trigger.new )
{
try
{
//find out whether there is a customer in the DB already, that uses the same title, first name and last name.
Customer__c dbc = [select First_Name__c, Last_Name__c, Title__c, Account__c from Customer__c];
//first name, last name and title must be unique!
if ( c.First_Name__c == dbc.First_Name__c && c.Last_Name__c == dbc.Last_Name__c && c.Title__c == dbc.Title__c )
{
//replace the account of the last added customer if the priority is less than that of the previous entry
if ( c.Account__r.Priority__c > dbc.Account__r.Priority__c )
{
c.Account__r = dbc.Account__r;
}
//delete dbc if the first name, last name and title are the same as a previous entry.
delete dbc;
}
} catch ( Exception e )
{
//oops, can't access the database, or something else went wrong.
c.addError( e.getMessage( ) );
}
}
}
the problem is, when I try to create two customers with the same name and title, I get the error: SObject row was retrieved via SOQL without querying the requested field: Customer__c.Account__r which I guess is produced because I don't specifically query the Account__r. The problem is, if I do this, I get an error that there is no Account__r in the custom object customer and I'm absolutely stuck. Anyone knows how to fix this problem?
..., Title__c , Account__r.Priority__c from Customer__c
in the query. But do reconsider the overall algorithm too as mentioned in my first comment.