Configure Change Tracking

Change history can be configured two different ways: using a simple trigger or enhancing the triggers with change data capture (CDC). A trigger is very easy to set up and is compatible with all PCS setups but can cause large operations, such as bulk imports or updates of data, to take slightly longer to complete. Enhancing change tracking with CDC requires an enterprise version of Microsoft SQL Server and some advanced configurations, including granting elevated SQL rights to PCS users, but has key advantages. When CDC is enabled, performance is optimized for recording changes and you have more control over the configuration and performance of the change tracking process.

You can configure CDC-based change tracking for all track-able tables in the database or for a subset of the tables. It is recommended to configure CDC-based tracking for any table that undergoes high-volume operations. Any table that PCS tracks but is not configured for CDC-based tracking will use a trigger to record the database changes. If a table or field is set up to be tracked using CDC but System-Enable Change History is set to No in PCS, records of the changes will not be accessible from within PCS and may not be retained.

If you need help in determining which configuration is best for your company, refer to the following decision tree.

Change Tracking Configuration Decision Tree

To set up or modify change tracking with a trigger or CDC, refer to the following topics:

• Set Up Change Tracking with a Simple Trigger

• Set Up Enhanced Change Tracking with CDC

It is recommended that change tracking configurations are made during the initial configuration of PCS or while performing system maintenance. A field becomes locked while its change history setting is modified in Field and UDF Customizations. Users are prevented from adding, modifying, or deleting data that contains the field.

If SQL Server Agent is not running, changes to data in CDC-enabled tables will still be tracked. However, they will not be pulled into PCS until the SQL Server Agent is restored. If the SQL Server Agent is down for greater than 60 days, changes that were made more than 60 days ago will be lost.