Refresh Triggers

The two refresh mechanisms — do not conflate them#

Refresh offset/period (on measures via their time-scheme attachment, and on event schemes) is the blanket mechanism: every pass, re-examine the trailing window of this size. It catches routinely-late data. Its cost is proportional to the window, every single pass.

Refresh triggers are the targeted mechanism: a separate trigger — a tag condition or a query — whose firing tells the engine "this specific thing changed; requeue it." They catch changes that arrive far too late for any sane offset (a batch record corrected days later, an MES row completed retrospectively) without paying a giant window every pass.

The design rule: offsets for predictable lateness measured in minutes-to-hours; refresh triggers for event-driven lateness measured in days.

Where refresh triggers exist#

Each carries its own connection reference (tag or query), a time scheme and interval, and — importantly — a template link, so refresh triggers are template members that instantiate and update like any other member.

The semantics that generate tickets#

A tag-based refresh trigger fires on its condition, like any trigger — and it needs the same discipline: a transition type and a hold time, or a noisy tag requeues the world continuously. If SQL Profiler shows your query-based event triggers running but never the refresh trigger's query, check the refresh trigger's own connection and condition are actually configured — an empty refresh trigger is silently inert.

What gets requeued is the containing period, not a surgical window. A refresh trigger firing against a weekly scheme requeues the containing week — not one hour around the change. Size expectations (and engine load) accordingly: a refresh trigger on a coarse calendar is a broad instrument.

Template inheritance has history. Refresh triggers are template-linked, and two defect classes have shipped: instances not receiving the template's refresh trigger on instantiate/update, and duplicate refresh triggers accumulating per time scheme — one visible, both firing, producing double requeue load with no visible cause. Both are fixed in the 7.1.7-era refactor (deduplication plus a unique index). On earlier versions: after any template update, verify each instance has exactly one refresh trigger per trigger per scheme, and expect a repair step during upgrade.

They can vanish in edits. A refresh trigger hangs off its parent trigger/measure configuration; operations that rebuild the parent (retrieval-type changes, some template updates on affected versions) have dropped them. After structural edits to a templated trigger, re-verify the refresh trigger survived — it fails silent, as a return to "late data never arrives".

Configuring one, practically#

For the common case — an MES table whose rows are corrected after the fact, driving a batch event:

1. The event's start/end triggers query the table (see Event SQL Patterns).
2. Add a refresh trigger on the same connection whose query returns the modification moments:

SELECT LastModified AS [DateTime]
FROM   Batches
WHERE  LastModified >  [PeriodStart]
  AND  LastModified <= [PeriodEnd]
  AND  LastModified > BatchEnd          -- only genuine after-the-fact edits

3. Keep the scheme's Refresh Offset modest (normal lateness only) — the refresh trigger now carries the deep corrections.

For measures, the equivalent lives on the measure's refresh configuration: an additional refresh entry (offset + period, optionally against a connection) per time scheme. The same trade applies — a standing wide offset versus a targeted requeue.

Verifying they work#

Refresh triggers fail invisibly, so verify by effect:

1. Make a controlled late change at the source (edit one historical row / force the tag condition).
2. Watch the Monitor for the requeue and the affected period's reprocessing.
3. Confirm the event/measure now reflects the change — and note the wall-clock lag, which is your real-world correction latency.

If step 2 shows nothing: the trigger's connection, condition, or (on affected versions) its very existence on this instance is the problem — in that order.

See also#

• Event Triggers
• Event SQL Patterns
• Backfilling and Recalculation
• Designing an Event Scheme