Hey
When using Sequel on MSSQL, adding any triggers on tables will break any
actions on models / tables that results in a UPDATE or INSERT statement.
This is due to how Sequel formats the INSERT / UPDATE statement:
```
INSERT INTO [TABLE]([C1]) OUTPUT [INSERTED].* VALUES ('SOME VALUE')
```
This results in an error stating:
```
Sequel::DatabaseError - TinyTds::Error: The target table 'table' of the DML
statement cannot have any enabled triggers if the statement contains an
OUTPUT clause without INTO clause.
```
The Microsoft recommended fix for getting this error is to add the INTO
clause mentioned in the error message:
```
DECLARE @p1 TABLE (col2 VARCHAR(30))
INSERT INTO [TABLE]([C1]) OUTPUT (INSERTED.*) INTO @p1 VALUES ('SOME VALUE')
```
Jeremy previously stated that the first (broken form) is more performant
(and in my opinion simpler), but it completely excludes the use of Sequel
on tables with triggers.
So I'm posting this with the hopes of getting some feedback from other
Sequel / MSSQL users to in the long term find a workable solution to run
Sequel on tables with triggers.
For completeness, here's a Microsoft blog post
<https://blogs.msdn.microsoft.com/sqlprogrammability/2008/07/11/update-with-output-clause-triggers-and-sqlmoreresults/>
discussing the issue, and a StackOverflow question
<https://stackoverflow.com/questions/13198476/cannot-use-update-with-output-clause-when-a-trigger-is-on-the-table>
regarding it too. As well these issues on gihub: #335
<https://github.com/jeremyevans/sequel/issues/335> and #967
<https://github.com/jeremyevans/sequel/issues/967>
I'll be working on a workaround for this, hopefully in the next couple of
weeks, and will post the workaround once ready.
Regards,
Jrgns
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/cc2490ba-d1b9-4944-a6cf-f0019792e053%40googlegroups.com.
