Rails 8: SQL Server trigger-fired INSERT returns array-wrapped identity ([1]), causing NoMethodError in integer deserialization

[djberg96]

Howdy folks.

Real person here who had to get some help from AI for this one, hope that's ok since there was no way I would've figured this one out on my own.

The short version is we're trying to upgrade from Rails 7 to Rails 8 and hitting an unusual issue with triggers. Most of the text below was crafted by GPT 5.5 under my guidance:

Title

Rails 8 + activerecord-sqlserver-adapter 8.0.10: triggered INSERT can return array-wrapped identity ([1]) and fail integer deserialization

Summary

During a Rails 7 to Rails 8 upgrade, we observed INSERT failures only when an AFTER INSERT trigger fires on SQL Server. In failing cases, the adapter returns an array-wrapped value for the inserted identity, and Rails 8 raises:

NoMethodError: undefined method to_i for an instance of Array

Non-trigger inserts continue to work.

Environment

• Rails: 8.0.5
• activerecord-sqlserver-adapter: 8.0.10
• Ruby: 3.3.x
• SQL Server: 2022

Steps To Reproduce

1. Use Rails 8 and activerecord-sqlserver-adapter 8.0.10.
2. Set ActiveRecord::ConnectionAdapters::SQLServerAdapter.use_output_inserted = false.
3. Create a table with identity PK and an AFTER INSERT trigger that performs additional DML.
4. Insert one row where trigger criteria does not match.
5. Insert one row where trigger criteria matches.
6. Instrument adapter methods last_inserted_id and returning_column_values to inspect returned shapes.

Minimal Trigger Shape (Anonymized)

CREATE TRIGGER [dbo].[WorkflowTrigger_NEW] ON [dbo].[EventTable]
AFTER INSERT
AS
BEGIN
  INSERT [dbo].[WorkflowAudit]
  (event_id, workflow_state_id, action_id)
  SELECT i.id, wf.state_id, wf.action_id
  FROM inserted i
  LEFT JOIN [dbo].[WorkflowState] r ON r.id = i.state_id
  JOIN [dbo].[WorkflowDefinition] wf ON wf.state_id = r.id
  WHERE wf.criteria IN ('Shared', 'Accessed', 'Archived')
END

Expected Behavior

Both inserts return scalar identity values and succeed, regardless of trigger execution.

Actual Behavior

When trigger does not fire:

last_inserted_id => 583 (Integer)
returning_column_values => [583] (Array)
OK id=583 (Integer)

When trigger fires:

last_inserted_id => [1] (Array)
returning_column_values => [[1]] (Array)
FAIL NoMethodError: undefined method `to_i` for an instance of Array

Additional Confirmed Findings

In our testing environment:

• Session NOCOUNT: OFF
• Server default NOCOUNT: OFF
• Trigger Workflow: NOCOUNT not specified
• Trigger SetRequestOutcome: NOCOUNT not specified

So this is not caused by a session/server NOCOUNT ON configuration.

Why This Appears As Rails 7 vs Rails 8

The adapter return shape issue existed but was effectively tolerated in earlier behavior. Rails 8 stricter integer coercion now surfaces it immediately when an Array reaches integer deserialization.

Current Workaround

ActiveRecord::ConnectionAdapters::SQLServerAdapter.use_output_inserted = false

module SQLServerAdapterPatch
  def last_inserted_id(result)
    value = super
    value = value.first if value.is_a?(Array) && value.size == 1 && !value.first.is_a?(Array)
    value
  end

  def returning_column_values(result)
    values = super
    values = values.map { |v| v.is_a?(Array) && v.size == 1 ? v.first : v } if values.is_a?(Array)
    values
  end
end

ActiveRecord::ConnectionAdapters::SQLServerAdapter.prepend(SQLServerAdapterPatch)

Proposed Fix

Any of the following should address this class of issue:

1. Ignore trigger-generated intermediate result rows when computing identity return values.
2. Normalize last_inserted_id and returning_column_values so single identity paths never return array-wrapped scalars.
3. Add regression tests for AFTER INSERT triggers that emit extra result shapes when use_output_inserted = false.

Notes

• exclude_output_inserted_table_names did not resolve this by itself.
• use_output_inserted = false was necessary but not sufficient in this failing path.

Ok, it's me again, the human. If necessary I can checkin a minimal repo with docker, rails, etc to demonstrate the issue.

Or if this is something obvious, I'm all ears!

[aidanharan]

@djberg96 Would you be able to create a reproducible script that demonstrates the issue? You can find the template for it at https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/wiki/How-to-report-a-bug