Yo! So I’m running into a little issue I’d like to get some advice on.

I have an existing MSSQL database I’m retrofitting Elixir/Phx/Ecto on top of. I have to have tons of custom @primary_keys on, many of these tables have post insert/update triggers, which is where the issue stems from. The error I’m running into is when I insert the record, I usually get this error message:

(Tds.Error) Line 1 (Error 334): The target table 'Users' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

The query usually looks like:

[debug] QUERY ERROR db=80.4ms
INSERT INTO [Users] ([EmailAddress], [FirstName], [LastName], [UserPassword]) OUTPUT INSERTED.[UserID] VALUES (@1, @2, @3, @4) ["kevin+000@kdsherm.com", "Kevin", "Sherman", "sup"]

This is similar to a few issues I’ve tracked down:
https://github.com/findmypast-oss/mssql_ecto/issues/27
https://github.com/livehelpnow/tds_ecto/issues/61

Now, I can get through the error message by doing something like @primary_key {:UserID, :integer, []} in my schemas, or Repo.insert(returning: false) this helps to remove the OUTPUT clause in the query and the record is saved in the database. Cool.

But the next issue is that okay, the record persists, but without the OUTPUT clause, I don’t get the generated UserID back from the query in the struct:

%EfAPI.Data.User{
  EmailAddress: "kevin+000@kdsherm.com",
  FirstName: "Kevin",
  LastName: "Sherman",
  UserID: nil,
  ZipCodeDesc1: nil,
  __meta__: #Ecto.Schema.Metadata<:loaded, "Users">,
}

So given that I’m in funky territory with an existing database with triggers (I knowwww, but not a lot I can do about triggers for a while), what’s a good strategy to get the record I just created?

My two thoughts: if I validate the uniqueness of the email, I could use the email address to pull up the record; second was I need to generate password hashes, those should be unique, maybe I could use the password hash in a query to pull the full record? Or am I missing a slick idea ?

Bonus thought: I see an INTO clause in the query, so what’s the deal there?!

Thanks all!