You can make MDrivenServer refresh objects that another application has changed directly in a SQL Server database; this page is for solutions where MDriven and an external system share selected tables.

What SQL Server change tracking solves

MDrivenServer has its own built-in change tracking: it maintains a dirty list of objects that clients can request. When a client receives that list, it discards matching cached objects. If an affected object is currently displayed, the client reloads it from MDrivenServer and SQL Server.

That built-in dirty list only contains changes made through MDriven. MDrivenServer cannot detect a direct UPDATE, INSERT, or DELETE performed by a legacy application, integration, trigger, or other service against the SQL tables.

SQL Server change tracking provides the missing input. A pair of MDrivenServer server-side jobs:

1. asks SQL Server whether its change-tracking version has advanced;
2. when it has, queries the tracked tables for changed primary keys; and
3. calls SuspectExternalUpdateInvalidate for the corresponding MDriven objects.

For example, if an external service updates the row for a Customer, the invalidation job identifies that row by its primary key. At the next client refresh, clients that have that Customer cached receive the invalidation and reread it.

Scope and prerequisites

SQL Server change tracking is available from SQL Server 2008 and in Azure SQL. It is also available in Express editions, including the database used by Turnkey.

Enable it only for tables that another system can change outside MDriven. Tracking every table adds unnecessary work when MDriven is the only writer.

Requirement	Why it matters
A SQL Server or Azure SQL database	This implementation uses SQL Server change-tracking functions and CHANGETABLE.
A primary key for every tracked table	The invalidation operation needs the primary key of each changed row to identify the corresponding object.
A MDrivenServer server-side job for each phase	One job reads the current version; a second job invalidates objects when that version changes.
Change tracking enabled on the database and selected tables	SQL Server only returns changes for tables on which change tracking is enabled.

This pattern is not tied to how changes are detected. If you use another database server or another detection mechanism, you can still use SuspectExternalUpdateInvalidate when you can obtain the primary keys of changed rows.

How the pattern works

SQL Server assigns a version number to tracked changes. The implementation keeps two version values in a singleton object:

Attribute	Type	Purpose
CurrentVersionNumberFromChangeTracking	Int64?	The most recently read value from CHANGE_TRACKING_CURRENT_VERSION().
LastHandledVersionNumberFromChangeTracking	Int64?	The latest version that the invalidation job has finished processing.

The current-version job performs one small query. The invalidation job runs only when the two values differ. It takes a copy of the current version before querying tables, uses CHANGETABLE (CHANGES ...) to obtain changed primary keys for each tracked table, invalidates the matching objects, and then records the copied version as handled.

Taking the copy first is important. Changes can occur while the job is running. Those later changes remain for the next run rather than being marked handled prematurely.

Enable change tracking in SQL Server

Before configuring MDriven, enable change tracking at database level and on every table that MDriven must monitor.

1. Connect to the target database with a SQL Server administration tool.
2. Enable database-level change tracking. Replace <database name> with the database name.
3. Enable tracking on each selected table. Replace <table name> with the physical SQL table name.

ALTER DATABASE <database name>
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

ALTER TABLE <table name>
ENABLE CHANGE_TRACKING;

Do not enable column tracking for this pattern. MDriven invalidates and rereads complete objects, so it tracks rows rather than individual columns.

The CHANGE_RETENTION value must be long enough for the invalidation job to process changes reliably. The example uses two days. If the job is stopped longer than the retention period, SQL Server may no longer retain the changes needed to advance from the last handled version.

To remove change tracking later, use HowTos:Remove Change Tracking in SQL Database.

Add state to the model

Add these nullable Int64 attributes to SysSingleton:

• CurrentVersionNumberFromChangeTracking
• LastHandledVersionNumberFromChangeTracking

SysSingleton supplies one persistent place to store the versions across server-side job runs.

Create the current-version server-side job

Create a server-side ViewModel that reads the current SQL Server change-tracking version.

1. Create a ViewModel named SS_GetCurrentTrackingVersion, rooted in SysSingleton.
2. Add CurrentVersionNumberFromChangeTracking to the ViewModel.
3. Add an action named GetCurrentVersion with the following EAL:

self.CurrentVersionNumberFromChangeTracking := SysSingleton.sqlpassthrough(
'-- Get the current tracking version number
  DECLARE @CurrentVersionNumberFromChangeTracking bigint;
  SET @CurrentVersionNumberFromChangeTracking = CHANGE_TRACKING_CURRENT_VERSION();
  select @CurrentVersionNumberFromChangeTracking', Int64 )->first.Part1

1. Add criteria for server-side execution using this PS OCL:

SysSingleton.allInstances

This job reads one version value. Schedule it before the invalidation job.

Create the invalidation server-side job

Create a second server-side ViewModel that retrieves changed row keys and invalidates their MDriven objects.

1. Create a ViewModel named SS_InvalidateExternalChanges, rooted in SysSingleton.
2. Add CurrentVersionNumberFromChangeTracking and LastHandledVersionNumberFromChangeTracking to the ViewModel.
3. Add an Int64 variable named vHandleToVersionNumber.
4. Add an action named Invalidate.
5. Start the action by copying the current version to the variable:

-- Move to variable avoiding change of version during execution
vHandleToVersionNumber := vCurrent_SS_InvalidateExternalChanges.CurrentVersionNumberFromChangeTracking;

1. Add one invalidation line for each tracked class/table. Replace the placeholders with the MDriven class name, its physical primary-key column, and its physical SQL table name:

selfVM.SuspectExternalUpdateInvalidate(
  <classname>.sqlpassthroughobjects(
    'SELECT <primarykeyattributename> FROM CHANGETABLE (CHANGES <tablename>, ' +
    LastHandledVersionNumberFromChangeTracking.toString +
    ') AS C'
  )
);

For example, a class CV stored in table [CV] with primary key [CV_ID] uses:

selfVM.SuspectExternalUpdateInvalidate(CV.sqlpassthroughobjects(
  'SELECT [CV_ID] FROM CHANGETABLE (CHANGES [CV], ' +
  LastHandledVersionNumberFromChangeTracking.toString +
  ') AS C') );

1. After all tracked classes have been processed, update the handled version:

-- Update last handled change number
self.LastHandledVersionNumberFromChangeTracking := vHandleToVersionNumber

1. Add this PS OCL as the criteria for server-side execution:

SysSingleton.allInstances->select(ss|ss.CurrentVersionNumberFromChangeTracking <> ss.LastHandledVersionNumberFromChangeTracking)

The criteria prevents the invalidation job from running when no new change-tracking version exists.

Add one line per tracked table

Each SuspectExternalUpdateInvalidate call must use the class that maps to the table and select that table's physical primary-key column. If external systems can change Customer and Order, add two calls: one that queries CHANGETABLE for Customer and one for Order.

Do not use a class name, table name, or primary-key name from an example without checking the mapping in your model and database. See Documentation:SQL Database for how classes are stored in the SQL database.

Generate table SQL and invalidation lines from tagged values

When many classes are tracked, tag the relevant classes and generate both the SQL setup statements and EAL lines from the model. This helps keep class, table, and primary-key mappings aligned, especially for reverse-engineered databases with non-standard names.

1. Add a tagged value named ChangeTracking to every class that should be tracked.
2. In the model debugger, evaluate the following OCL to generate ALTER TABLE statements:

Class.allInstances->select(s | s.TaggedValue->select(tv | tv.Tag.toUpper='CHANGETRACKING')->notEmpty)->collect(c |
  String.format('ALTER TABLE {2} ENABLE CHANGE_TRACKING  -- For class {0}',
    c.Name,
    c.TaggedValue->select(tv | tv.Tag='Eco.PrimaryKey')->first.Value,
    c.TaggedValue->select(tv | tv.Tag='Eco.TableName')->first.Value
  )
)->orderBy(s|s)

For a class CV, this can produce:

ALTER TABLE [CV] ENABLE CHANGE_TRACKING  -- For class CV

1. Run the generated SQL against the target SQL Server database.
2. Evaluate the following OCL to generate the invalidation lines for the Invalidate action:

Class.allInstances->select(s | s.TaggedValue->select(tv | tv.Tag.toUpper='CHANGETRACKING')->notEmpty)->collect(c |
  String.format('selfVM.SuspectExternalUpdateInvalidate({0}.sqlpassthroughobjects( \'SELECT {1} FROM CHANGETABLE (CHANGES {2}, \' + LastHandledVersionNumberFromChangeTracking.toString + \') AS C\') );',
    c.Name,
    c.TaggedValue->select(tv | tv.Tag='Eco.PrimaryKey')->first.Value,
    c.TaggedValue->select(tv | tv.Tag='Eco.TableName')->first.Value
  )
)->orderBy(s|s)

1. Insert the generated lines into SS_InvalidateExternalChanges.Invalidate before the statement that updates LastHandledVersionNumberFromChangeTracking.

Schedule and verify the jobs

Schedule the current-version job and invalidation job at an interval that meets the application's freshness requirement and SQL Server capacity. The operations are designed to be small: the version check can run in about 10 ms, and checking approximately 40 unchanged tables can take about 100 ms. An interval of five seconds is one example; choose an interval appropriate for the required discovery time and server load.

Verify the configuration with a controlled external update:

1. Open an object through a MDriven client so that it is cached or visible.
2. Update that object's row directly through the external system or SQL Server.
3. Wait for the current-version and invalidation jobs to run.
4. Refresh the client. The client should receive the invalidation and reread the object.

Clients receive invalidation information on their next refresh. An object is not pushed into an already rendered client state without that refresh.

Operational notes

• Enable tracking only on tables that non-MDriven writers can affect.
• Include every changed class/table in the invalidation action. Enabling SQL tracking without adding its corresponding invalidation line does not notify MDriven clients.
• Run the version-reader job before the invalidation job. The second job depends on the current version recorded by the first.
• Keep the job schedule inside the SQL Server change-retention window.
• Direct SQL changes can bypass model-level behavior. This feature keeps cached objects current; it does not make external writers execute MDriven validation or other model behavior.
• If database mappings change, regenerate or review the generated table and primary-key references. Use Documentation:Database evolve when evolving the database to match model changes.

See also

• Documentation:OCLOperators SuspectExternalUpdateInvalidate
• Documentation:SQL Server
• Documentation:SQL Database
• Documentation:Database evolve
• HowTos:Remove Change Tracking in SQL Database