please see my scenario. I have 2 custom objects A(child) and B(parent>. Date model is such that Account is the master detail parent of A, while B is the lookup parent of A. The objects Account and B are hence related to each other by this object A which acts as a junction.
The requirement was that no two records of the object A, under the same account and same B should have the same name. I implemented this using a before insert,before update trigger on the object A(child). The logic is as follows:
- Created a Set that will store a concatenated value of IDs and Name
- Queried all the records of object A from the database(for update-> didn't query the current records)
- Iterated through the query result, added entries in the Set of the form => Account ID + B Record ID + A Name
- Iterated through trigger.new and compared the [[ Account ID + B Record ID + A Name ]] of every record in trigger.new, using .contains to see if it exists in the set.
- If the value exists => addError
I feel like the algorithm is inefficient, because we query all the records of the object A for just this purpose. We are using from 50,000 max query rows in a transaction gov limit. There are other features that follow this on the same trigger too, so I am worried about hitting the 50,000 row governor limit if the number of records of the object A increase to say 10K.
Is there any way to implement this better, may be using duplication rules?
In general, what are the safeguards or design patterns one can put up against hitting the 50,000 row SOQL limit when the number of records in the database increases. Thank you for your patience.