On Saturday, December 21, 2019 at 3:08:07 AM UTC-8, Jurgens du Toit wrote:
>
> 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.
>
The simple existing workable solution is to disable the use of OUTPUT for
the tables using triggers using Dataset#disable_insert_output. I think you
can disable this for all model inserts for a given Database via:
DB.extend_datasets do
def supports_insert_select?; false end
end
Unfortunately, the second approach is not general, since you would have to
know the types of all the columns, and that isn't available in the general
case, though it may be possible for simple cases.
I'm definitely open to changes regarding this behavior, as long as they are
backwards compatible and do not negatively affect performance for users not
using triggers.
Thanks,
Jeremy
--
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/68f02f7b-286f-4b97-82c5-9455a52c7993%40googlegroups.com.
