Note: This is all about UNSUPPORTED database level updates. These kind of updates are not recommended (unless you are left with no options).
Though any kind of database level updates are not recommended in CRM, there are scenarios where we are required to do so. I had one such requirement where I was required to perform a simple update (setting two options field value to Yes) for millions of records. Doing same using a supportive way (bulk update or bulk workflow execution) used to take days due to CRM service calls but same updates using SQL script was done in few hours.
I had auditing enabled for these records in CRM, but as this was unsupported db level update I was not expecting any help from audit history. What was expected form auditing was either of below:
- Change track as new update request in audit history. (If there is any kind of trigger return at db level to maintain auditing)
- No updates at all in audit history. (Assuming auditing are maintained by some kind of workflow/plugin that trigger at UI level)
Unfortunately, none of this happened and what happened was not at all audit friendly or expected. This is what happened:
The change made by SQL script was tracked at create event itself in audit history. But only the field value change was tracked and not the change date/time. The change date/time was still showing when the record was created.
Though these kind of changes are not recommended, but I still feel that in some cases these are required and this behavior of auditing will affect the consistency. I feel that things should be either completely baked or not baked at all than having them half baked. But again this is only what I feel. J
I created a Contact record on 7/6/2015 at 11:52 AM with ‘First R~~~~~ Created’ set to ‘No’. Audit history shows something like below.
Now on 7/6/2015 at 12:00 Noon, I update the value for ‘First R~~~~~ created’ to ‘Yes’ using a database level update script. Ideally there should have been a new entry made in audit log to track this change and there should be no change made to existing audit history records. But CRM updates the changed value in create event itself without even tracking the change time.
Hope this helps and thanks if you have come all the way down here reading the article.