Tds.Ecto: custom primary key and OUTPUT/INSERT DML statement error

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 :tm:?

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

Thanks all!

2 Likes

Hi @ksherman, there seems to be some improvements in tds library that could fixed this issue. Wouldn’t it be too much to ask if you can try your scenario with ecto_sql fork here

I just tried it out and getting the same error, however I’m also not convinced the trigger is written in a way to support returning the triggered record. I’ll pass this along to our DBA to look at OUTPUT statements to see if the trigger needs to be improved.

If it is not too much to ask, could you please create dummy example with same issue (table and triger)? I’m just curious how TDS message looks like. You can send me example in PM.

(Just guessing) It could be that triggers DONE token is streamed by sql server after column definition (COL_INFO token) so insert ROWS and DONE tokens from insert are pushed back in stream and are ignored in result. If I can see flags in DONE token, maybe there is something that I could do