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

ksherman · March 8, 2019, 11:02pm

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:

github.com/findmypast-oss/mssql_ecto

DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

opened 06:05PM - 20 Sep 17 UTC

LoganBresnahan

bug

### Problem: When a database table has a trigger attached to it, `insert/1` wil…l fail because the adapter is not utilizing an `INTO` clause. This blog post from the SQL Server team explains the issue well: https://blogs.msdn.microsoft.com/sqlprogrammability/2008/07/11/update-with-output-clause-triggers-and-sqlmoreresults/ ### Error Example: ```Elixir m=%{ client_sport_id: 1, user_id_entered: 1, tracking_type: "TT-mon", subject: "Incoming Call", notes: "This is a note.", tracking_date: "2017-09-14T11:21:39.592873Z", entry_date: "2017-09-14T11:21:39.592873Z" } cs=OpsMessagingService.ClientTracking.changeset(%OpsMessagingService.ClientTracking{},m) OpsMessagingService.Repo.insert(cs) ``` result: ``` insert/1 error: ** (Mssqlex.Error) [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The target table 'client_tracking' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause. | ODBC_CODE 42000 | SQL_SERVER_CODE 334 (ecto) lib/ecto/adapters/sql.ex:571: Ecto.Adapters.SQL.struct/7 (ecto) lib/ecto/repo/schema.ex:469: Ecto.Repo.Schema.apply/4 (ecto) lib/ecto/repo/schema.ex:205: anonymous fn/13 in Ecto.Repo.Schema.do_insert/4 ``` ### DB Trigger Example: ```SQL CREATE TRIGGER [dbo].[briu_client_tracking] on [dbo].[client_tracking] for INSERT,UPDATE as begin declare @icnt int, @dcnt int, @errno int, @errmsg varchar(255) begin select @icnt = count(*) from inserted; select @dcnt = count(*) from deleted; IF @icnt > 0 and @dcnt=0 begin update client_tracking set entry_date=current_timestamp, mod_date=current_timestamp where client_tracking_id in (select client_tracking_id from inserted) end IF @dcnt > 0 begin update client_tracking set mod_date=current_timestamp where client_tracking_id in (select client_tracking_id from deleted) end end return end ``` ### Temporary Workaround: Even if you're only looking to insert one record, you can use `insert_all/2` and pass in your one entry because it does not rely on a return value from the database. https://hexdocs.pm/ecto/Ecto.Repo.html#c:insert_all/3 ### Environment: * MssqlEcto version: 0.1.4 * Ecto version: 2.1.6 * Elixir version: 1.4 * Erlang/OTP version: 19 * Microsoft SQL Server version: Microsoft SQL Server 2014 (SP2-CU5) - 12.0.5546.0 (X64) * Operating System and version: Ubuntu 16.04.3 LTS

github.com/livehelpnow/tds_ecto

insert data error for a table with a trigger

opened 05:45AM - 27 Mar 18 UTC

closed 03:50PM - 11 May 18 UTC

toreal

We have a table that has a trigger with it. We defined its schema as @pr…imary_key true schema "mytable" do field :myfield, :string end When I insert a new row. the sql command is as follows. ` INSERT INTO [mytable] ([myfield]) OUTPUT INSERTED.[id] VALUES (@1) ` It will produce following error: The target table 'mytable' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause To avoid this situation, we set @primary_key false. The sql becomes that (without OUTPUT): ` INSERT INTO [mytable] ([myfield]) VALUES (@1) ` However, the error message becomes: Ecto.StaleEntryError : attempted to insert a stale struct: What should we do to insert a new row? I've debugged the issue a little and found that after calling the query method of the Sql adapter the result has num_rows set to zero. ` %Tds.Result{columns: ["id"], command: nil, num_rows: 0, rows: [[1]]} ` Anyway, the record is created. The result is similar with [issue 42](https://github.com/livehelpnow/tds_ecto/issues/42).

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!

mjaric · March 1, 2020, 12:00pm

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

ksherman · March 2, 2020, 3:44pm

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.

mjaric · March 2, 2020, 8:59pm

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