Database triggers
Several Genie capabilities are enforced in the database, not the app — because a row can be mutated through many non-converging paths (form submit, row delete, LINQ, import, raw SQL) and the database is the only place that sees them all. Each capability installs a per-entity trigger, created only when the entity opts into it. This page maps the four trigger kinds, how they’re created, how they avoid stepping on each other, and what they cost.
The map
Section titled “The map”Each declaration on an entity independently creates its own trigger, wired through the same migration pipeline. An entity carries 0 to 4 triggers depending on what it declares.
The four triggers
Section titled “The four triggers”All are per-entity, dual-dialect (SQL Server and PostgreSQL), and idempotent — re-created on each entity migration and dropped cleanly when the capability is turned off.
| Trigger | Created when | SQL Server | PostgreSQL | What it does |
|---|---|---|---|---|
trg_{t}_GenerateSequence |
entity has a <Sequence> field |
AFTER INSERT (statement-level, set-based) |
AFTER INSERT … FOR EACH STATEMENT |
Expands the cooked sequence token into the real auto-number (batch reserve + ROW_NUMBER()) |
trg_{t}_Syncable |
entity declares <Search> or Warehousing="Polling" |
AFTER INSERT, UPDATE |
BEFORE INSERT OR UPDATE … FOR EACH ROW (inline NEW) |
Flags the row IsSyncable so the warehousing worker ships it to every configured target |
trg_{t}_AuditLog |
Logging="Enabled" |
AFTER INSERT, UPDATE, DELETE (statement-level) |
AFTER INSERT OR UPDATE OR DELETE … FOR EACH ROW |
Writes before/after JSON directly into [Audit].[AuditLog] |
trg_{t}_RowVersion |
Concurrency="Enabled" |
AFTER UPDATE (self-UPDATE +1, TRIGGER_NESTLEVEL guard) |
BEFORE UPDATE … FOR EACH ROW (inline NEW.RowVersion = OLD+1) |
Advances the optimistic-concurrency stamp on every update |
The Concurrency trigger also ensures a DEFAULT 0 constraint on the RowVersion column at migration
time (that’s DDL, not a trigger — EF can’t set a default on a [ConcurrencyCheck] token).
How they’re created
Section titled “How they’re created”The triggers are not part of the host’s EF migration. They’re (re)installed by the engine’s model
migration whenever an entity’s schema is applied — EntityMigrationStrategy
calls, in this fixed order:
EnsureSequenceTriggerAsyncEnsureSyncableTriggerAsyncEnsureAuditTriggerAsyncEnsureConcurrencyTriggerAsync
Each Ensure* is a no-op unless the entity declares the matching capability, and each is idempotent
(CREATE OR ALTER on SQL Server, CREATE OR REPLACE + DROP … IF EXISTS on PostgreSQL). Turning a
capability off drops that trigger on the next migration. The generated SQL is deterministic and unit-tested
per dialect.
Tables that are not in the model catalog
Section titled “Tables that are not in the model catalog”EntityMigrationStrategy walks the XML model catalog, so it never sees two other kinds of warehoused
table. Both get the same trg_{t}_Syncable trigger from somewhere else, at startup:
- The framework’s own tables (and a host’s hand-written
ISyncableentities) are read from the EF model by an engine SQL contributor,Warehousing.SyncableTriggers. - Wizard response tables are installed by the wizard provisioner as
Wizard.Resp{Name}.Warehousing, deliberately outside the shape gate that skips an unchanged wizard.
Both are recorded in Genie.ExecutedScripts and hash-gated there, so they cost one row read per boot
once installed and redeploy by themselves when the engine changes the trigger body. Unlike the catalog
path, neither drops a trigger — un-marking a table means removing its trigger in the same change.
EF Core interop (HasTrigger)
Section titled “EF Core interop (HasTrigger)”Although these triggers are installed by SQL and not by EF migrations, EF Core still needs to know
they exist. On SQL Server, EF’s default save pipeline uses an OUTPUT clause, which SQL Server
rejects on a table that has AFTER triggers (error 334) — so a host doing context.Add(...) /
Attach(...) + SaveChanges() on a logged, concurrent, sequenced, or searchable entity would fail.
The generated EF configuration handles this for you. When an entity declares any of the four
capabilities, its {Entity}Configurations class emits a TriggerNames override, and
EntityBaseConfiguration declares each name via ToTable(tb => tb.HasTrigger(...)). That switches
EF to a trigger-safe save strategy automatically — no host configuration required. (On PostgreSQL,
HasTrigger is a harmless no-op: Npgsql uses RETURNING, which tolerates triggers, so the same
compiled configuration is correct for both databases.)
The trigger names are computed from the same declarations that create the triggers, so a host can introspect the set through EF’s model metadata:
var triggers = context.Model .FindEntityType(typeof(Invoice))! .GetDeclaredTriggers();
bool hasTriggers = triggers.Any();How they coordinate
Section titled “How they coordinate”Two mechanisms keep the triggers from fighting each other when several fire on the same write:
Ordering. On SQL Server the sequence trigger is explicitly ordered to fire first on INSERT
(via sp_settriggerorder), so the auto-number is finalized before the audit trigger reads the committed
row image — the log records the real value (SKU-01), not the raw token.
The suppression sentinel. Some triggers issue an internal follow-up write that would otherwise re-fire the others:
- the sequence trigger’s finalize
UPDATE(sets the real number), and - the SQL Server syncable trigger’s
UPDATE IsSyncable = 1.
Each wraps that write in a scope-visible sentinel (a #genie_suppress_triggers temp table on SQL Server,
a genie.suppress_triggers GUC on PostgreSQL); the audit, concurrency, and search triggers all early-return
when it’s raised. Without it, a bulk insert would cascade — every inserted row would produce a phantom
“changed” audit row, a spurious RowVersion bump, and a redundant re-index — turning the insert quadratic.
The sentinel is scope-local, so it can never leak onto a pooled connection.
What they cost
Section titled “What they cost”They’re safe by construction, but not free — and the cost is very asymmetric across dialects and write patterns. Each capability is opt-in, so an entity with no flags carries zero trigger overhead.
Cost per write path, heaviest first:
| Path | What each trigger adds |
|---|---|
| INSERT | Audit is the heavyweight: full-row JSON serialization + (SQL Server) a clustered-PK re-seek to the base table per row + an insert into [Audit].[AuditLog] (its read indexes maintained in the transaction); LOB columns are serialized in. Sequence: a MERGE … WITH (HOLDLOCK) on the counter + a set-based finalize UPDATE. Syncable (SQL Server): an UPDATE IsSyncable = 1 (a second write; free on PostgreSQL’s BEFORE trigger). |
| UPDATE | Concurrency (SQL Server): a self-UPDATE +1 (a second write of the row; free on PostgreSQL). Audit: two JSON serializations (old + new) + a change compare. Search: as above. |
| DELETE | Audit only: one JSON of the old image + an [Audit].[AuditLog] insert. The others don’t hook DELETE. |
The dominant costs, in order:
- Audit JSON — amplified by wide/LOB tables, and on PostgreSQL it’s
FOR EACH ROW, so a bulk insert is row-by-row versus SQL Server’s single set-based statement. This is the biggest dialect gap. - Sequence
HOLDLOCK— serializes reservations per(Format, ScopeKey)group (deliberate, for gapless numbering), a contention point under many concurrent same-scope inserts. - SQL Server double-writes — the concurrency self-update and the search flag write add extra row rewrites + transaction log.
Empirical anchor. With the current design, the whole stack runs on the order of ~0.25 ms/row for a batched insert on SQL Server (a 1,000-row Product import’s posting step; see the changelog) — dominated by audit. The earlier un-suppressed cascade made the same import grow with the square of the batch size.
Keeping it cheap:
- Enable only the flags you use — each is a real per-write cost.
Loggingis the expensive one — the trigger writes directly into the indexed[Audit].[AuditLog]inside the transaction. Reserve it for entities you actually audit; on PostgreSQL the ledger isUNLOGGEDfor cheaper writes, and in production you can drop its read indexes if write throughput matters more.- Wide/LOB tables +
Loggingis the case to watch, especially on PostgreSQL (row-by-row). - Bulk paths benefit most from the set-based SQL Server triggers; PostgreSQL’s per-row audit scales less well on large batches.
See also
Section titled “See also”- Change logging (audit) · Search · Sequences · Concurrency
- Model migration — how entity schemas (and these triggers) are applied