Restore trigger-based primary key generation as opt-in `primary_key_trigger:` option

[yahonda]

Background

Trigger-based primary key generation was removed in 61f8a305 (2018, Rails 6 / adapter 6.0) without a prior deprecation cycle. Since then, several users have run into the missing functionality and reverse-engineered the fix in user space:

• Trigger based primary key sequence not returned when prefetch_primary_key? is false #2426 — Trigger based primary key sequence not returned when prefetch_primary_key? is false (reporter monkey-patches sql_for_insert to add RETURNING for String pk)
• Getting primary key with RETURNING INTO doesn't work in Rails 7 and later #2468 — Getting primary key with RETURNING INTO doesn't work in Rails 7 and later
• [Bug] Adapter 7.1.0 ignores self.sequence_name, includes ID in INSERT instead of using RETURNING ID (Rails 7.1) #2431 — Adapter 7.1.0 ignores self.sequence_name, includes ID in INSERT instead of using RETURNING ID

In retrospect, the 2018 removal was a breaking change without notice, and there is still demand for the trigger-based path — especially on Oracle 11.2 where GENERATED AS IDENTITY is not available, and for legacy schemas with custom triggers.

Relationship to identity work

• Phase 1 (Add explicit identity: option to create_table for Oracle 12.1+ #2579) — adds create_table :foo, identity: true for Oracle 12.1+ (GENERATED BY DEFAULT AS IDENTITY)
• Phase 2 (Phase 2: Auto-enable identity primary keys in Migration[8.2]+ (depends on Rails per-adapter-migration-compatibility) [DRAFT] #2596, draft) — auto-enables identity in Migration[8.2]+ (depends on unmerged Rails extension point)
• Phase 3 (this issue) — restore trigger-based PK generation as a symmetric opt-in

Proposal

Add a primary_key_trigger: true option to create_table, mirroring the shape of identity: true:

create_table :foo                              # sequence + prefetch (current default)
create_table :foo, identity: true              # identity column (Phase 1, Oracle 12.1+)
create_table :foo, primary_key_trigger: true   # sequence + BEFORE INSERT trigger + RETURNING

Behavior

• Creates the table with a NUMBER PK column
• Creates the backing <table>_seq sequence (same as today)
• Creates a BEFORE INSERT FOR EACH ROW trigger that populates :NEW.id from <table>_seq.NEXTVAL when the inserted value is NULL
• prefetch_primary_key? returns false for the table (so Rails issues INSERT without computing the id)
• INSERT uses RETURNING <pk> INTO :returning_id to round-trip the generated id

Validation (symmetric with identity:)

• primary_key_trigger: true + id: false → ArgumentError
• primary_key_trigger: true + non-:primary_key id: (e.g., :uuid, :integer) → ArgumentError
• primary_key_trigger: true + composite primary_key: → ArgumentError
• primary_key_trigger: true + identity: true → ArgumentError (mutually exclusive)

Detection / round-trip

• prefetch_primary_key? needs to detect trigger-based tables in addition to identity ones — query all_triggers for BEFORE INSERT FOR EACH ROW triggers tied to the PK column, and cache the result in @do_not_prefetch_primary_key alongside the identity check
• schema_dumper should emit , primary_key_trigger: true for round-trip
• structure_dump already supports trigger emission via structure_dump: db_stored_code

Implementation notes

• Most of the plumbing is still in place: create_sequence_and_trigger exists and is currently called for the default sequence-based path. The work is mostly in:
  • Adding the option parsing and validation in create_table
  • Adding the trigger detection in prefetch_primary_key? / a new helper
  • Emitting the option in schema_dumper
  • Spec coverage symmetric to identity_primary_key_spec.rb
• The sql_for_insert String-pk RETURNING fix shipped as part of Add explicit identity: option to create_table for Oracle 12.1+ #2579 already addresses the underlying gap reported in Trigger based primary key sequence not returned when prefetch_primary_key? is false #2426, so this issue is purely about restoring the create_table-level ergonomics.

Why now

• Symmetric API surface with identity: makes the adapter's behavior easier to reason about
• Provides a documented migration path for users still on Oracle 11.2 (where identity is not available) who want trigger-based ID generation without monkey-patching
• Resolves long-standing user-space workarounds (Trigger based primary key sequence not returned when prefetch_primary_key? is false #2426 etc.)