Impossible to insert data on SQLServer if table has triggers enabled

[AudriusButkevicius]

This is due to http://www.sql-server-helper.com/error-messages/msg-334.aspx

which is used here: https://github.com/JetBrains/Exposed/blob/b97f22d7c676098f21a148d99ba53c784010335b/exposed-core/src/main/kotlin/org/jetbrains/exposed/sql/statements/BatchInsertStatement.kt#L103

[AudriusButkevicius]

A partial work-around for this seems to be using IdTable specifying your own entity id, without setting the field to autoIncrement, oppose to IntIdTable et al, which all set autoIncrement() values.

I believe this requires you to specify the ID when creating the entity, but I believe sadly this doesn't work either, as SQL server does not permit picking your own primary key for a table unless you run:

SET IDENTITY_INSERT [Tablename] OFF;

which requires super-user rights.

[Tapac]

@AudriusButkevicius , thank you for a report. It will be possible to skip returning values with shouldReturnGeneratedValues param on batchInsert function on the next release. Please check it when release will be available.

[AudriusButkevicius]

So two issues:

1. I don't think this helps with the DAO approach
2. I don't think this will work, because even if you disable this, I think you need to provide the framework primary keys as you insert, based on the glance at the code, which even if I do, it won't work as SQLServer does not allow picking primary keys on insert, unless you run as superuser.

[AudriusButkevicius]

So tried marking the primary key as non-autoincrementable, which generates the correct SQL without the output clause, but then the framework blows up at:

https://github.com/JetBrains/Exposed/blob/ab8257a60a9f588a063eb1792e70bcbaf259ae13/exposed-core/src/main/kotlin/org/jetbrains/exposed/sql/ResultRow.kt#L60

[obabichevjb]

The problem is yet reproducible. The following test does it:

class ProductDto(val name: String, val price: Int)

    object Products : IntIdTable("product") {
        val name = varchar("name", length = 128)
        val price = integer("price")
    }

    object ProductAlerts : IntIdTable("product_alert") {
        val name = varchar("name", length = 128)
        val price = integer("price")
    }

    object ProductUpdates : IntIdTable("product_update") {
        val name = varchar("name", length = 128)
        val price = integer("price")
    }

    @Test
    fun testSqlServerTrigger() {
        withDb(TestDB.SQLSERVER) {
            addLogger(StdOutSqlLogger)
            SchemaUtils.drop(Products, ProductAlerts, ProductUpdates)
            SchemaUtils.create(Products, ProductAlerts, ProductUpdates)

            exec(
                """
                    create or alter trigger check_quantity on product
                    after insert, update as
                    begin
                    insert into product_alert (name, price)
                        select i.name, i.price
                        from inserted i
                        where i.price > 20
                    end;
                """.trimIndent()
            )

            ProductUpdates.batchInsert(listOf(ProductDto("#1", 30))) {
                this[ProductUpdates.name] = it.name
                this[ProductUpdates.price] = it.price
            }

            val updates = ProductUpdates.selectAll().map { ProductDto(it[ProductUpdates.name], it[ProductUpdates.price]) }
            Products.batchInsert(updates, shouldReturnGeneratedValues = false) {
                this[Products.name] = it.name
                this[Products.price] = it.price
            }

            val name = ProductAlerts.selectAll().first()[ProductAlerts.name]
            assertEquals("#1", name)
        }
    }

batchInsert() in this test creates sql statement:

SQL: INSERT INTO product ("name", price)  OUTPUT inserted.id AS GENERATED_KEYS VALUES('#1', 30)

that fails with the error from the provided link:

The target table 'product' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

But shouldReturnGeneratedValues actually fixes that problem, and if batch insert called like Products.batchInsert(updates, shouldReturnGeneratedValues = false) {...} the sql statement without OUTPUT is generated and it works without problems, trigger happens and test finished successfully.

Another problem is that it (as mentioned above) does not work with DAO:

// Adding entity class for Products table
 class Product(id: EntityID<Int>) : IntEntity(id) {
      var name by Products.name
      var price by Products.price

      companion object : IntEntityClass<Product>(Products)
  }

 and trying to make insert
updates.forEach { update ->
    Product.new {
        this.name = update.name
        this.price = update.price
    }
}

It causes the same error since under the hood inside EntityCache::flushInserts() it executes the following code:

val ids = executeAsPartOfEntityLifecycle {
    table.batchInsert(toFlush) { entry ->
        for ((c, v) in entry.writeValues) {
            this[c] = v
        }
    }
}

but here it relies on the returned ids, so it's not possible just to put here shouldReturnGeneratedValues = false.