insert data error for a table with a trigger

[toreal]

We have a table that has a trigger with it.

We defined its schema as @primary_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.

[mjaric]

I will check this but until then you can use TDS directly instead, if query is not too complex, or create stored procedure. BTW, your trigger, what it does, can you share code?

[toreal]

Thank you for your reply. The real version of the trigger involved more complex logic. However, you can try it by inserting a trivial trigger as follows. CREATE TABLE [dbo].[testTable] ( [Id] INT NOT NULL PRIMARY KEY IDENTITY, [f] NVARCHAR(50) NULL ) GO CREATE TRIGGER [dbo].[testTrigger_Table] ON [dbo].[testTable] FOR DELETE, INSERT, UPDATE AS BEGIN SET NoCount ON END

By the way, I don't know how to use TDS directly. Could you give a hint or example? Thank you very much.

[mjaric]

Sorry for delayed response. This is mssql limitation. I think the onlu way to insert in such table would be to use Ecto.Adapter.SQL.query directly since you have to capture @@IDENTITY variable.

Ecto.Adapter.Sql.query(repo, “Insert into tablexyz values (....); select @@IDENTITY AS id;”)

I havenr tried but that is the only option I think you have