bulkCreate + options.returning on a table with hasTrigger enabled does not work

What you are doing?

var Task = sequelize.define('task', {
    title: Sequelize.STRING,
    rating: { type: Sequelize.STRING, defaultValue: 3 }
}, {
    hasTrigger: true
});

Task.bulkCreate([{
    title: "Fix bug",
    rating: "5"
}, {
    title: "Write documentation",
    rating: "1"
}], {
    returning: true
});
// Error happens

What do you expect to happen?

Promise should return a list of successfully inserted instances with resulting values of autoIncrement attributes.

What is actually happening?

Unhandled rejection SequelizeDatabaseError: The target table '%TABLE_NAME_HERE%' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Dialect: mssql
Database version: SQL Server 2014
Sequelize version: 3.24.3

I've just realised a way to circumvent this issue.

Instead of {returning: true}, use {individualHooks: true}.

Caveat

The difference is in the number of SQL queries.

For example, I have two records to bulkCreate. The correct bulkCreate will have one INSERT INTO statement, but this solution will have two INSERT INTO statements instead.**

If any of the INSERT INTO statements fails, there will be an error even if other record(s) was inserted successfully.

To anyone who really needs help with this, wrap the bulkCreate statement with transaction:

(Important: Performance is not guaranteed)

sequelize.transaction().then(function (t) {// Use a promise-based transaction
    Task.bulkCreate([{
        title: "Fix bug",
        rating: "5"
      }, {
        title: "Write documentation",
        rating: "1"
      }], {
        individualHooks: true, // This returns autoincrement keys
        transaction: t // This ensures if one statement fails, all fail
    }).then(function (response) {
      t.commit(); // Manually commit if there are no errors
    }).catch(function (err) {
      t.rollback(); // Manually rollback if there is any error(s)
    });
});

IndividualHooks did help me work around this, but it did so by destroying the bulkCreate behavior and causing 1 insert for each record. I'm looking into query-generator.js. It looks like bulkInsertQuery() doesn't have the tmp table logic included in insertQuery (based on hasTrigger). Can anyone confirm this before I start on a PR? There already a branch addressing this?

@rainabba

I will reopen this since it is still an issue.

Hi @rainabba! Thanks for being willing to help. If you can create a PR, that would be great!! Please look at PRs 7095 and 7096 before starting, perhaps you can use some of those ideas. Thanks! Let me know if you need further help.

No prediction when this will be solved?

I have a similar issue, but even with "individualHooks: true", I receive the same error, the difference is I'm using include that has an Sequelize Model that has triggers.

Is there a work around?

I can confirm this remains reproducible with the current npm latest v7 packages: @sequelize/core and @sequelize/mssql 7.0.0-alpha.48.

SSCCE: sequelize/sequelize-sscce#302
CI: https://github.com/sequelize/sequelize-sscce/actions/runs/31762488431

The SSCCE uses a model declared with @Table({ hasTrigger: true }), a real SQL Server AFTER INSERT trigger, and Foo.bulkCreate([{ name: 'trigger test' }]). On SQL Server 2022 / Node 18 and 20, startup succeeds and Sequelize emits:

INSERT INTO [trigger_bulk_create_test] ([name]) OUTPUT INSERTED.[id], INSERTED.[name] VALUES (N'trigger test');

SQL Server rejects it with error 334:

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

Expected: bulkCreate for a trigger-bearing MSSQL model should avoid a bare OUTPUT clause while preserving its generated-value result semantics. The SSCCE intentionally asserts the generated ID after bulkCreate so a fix must preserve that contract.

Current application workaround

Until Sequelize provides that behavior, our application defines this @BeforeBulkCreate hook on a shared BaseModel that every application model inherits:

@BeforeBulkCreate
static suppressOutputForTriggerModels(models: Model[], options: BulkCreateOptions) {
  if (!models[0].modelDefinition.options.hasTrigger) return;

  options.returning = false;
}

The inherited hook applies only to trigger-bearing bulk inserts. It avoids the invalid SQL while retaining one bulk operation and its normal bulk hooks; it deliberately does not return database-generated values. It is a workaround, not a replacement for Sequelize supporting generated-value results on trigger-bearing MSSQL bulk inserts.

Environment: @sequelize/core 7.0.0-alpha.48; @sequelize/mssql 7.0.0-alpha.48; tedious 19.2.2; Node.js 18 and 20; SQL Server 2017 and 2022 CI containers.

I read the contribution guidelines.
I provided an executable SSCCE and independent CI result.
I am not currently able to submit a fix PR.

Navigation Menu

Repository navigation

bulkCreate + options.returning on a table with hasTrigger enabled does not work #7075

What you are doing?

What do you expect to happen?

What is actually happening?

Caveat

Current application workaround

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

bulkCreate + options.returning on a table with hasTrigger enabled does not work #7075

Description

What you are doing?

What do you expect to happen?

What is actually happening?

Activity

iamjoyce commented on Jan 6, 2017

Caveat

rainabba commented on Sep 13, 2019

papb commented on Sep 16, 2019

Judahh commented on Jul 18, 2023

Judahh commented on Jul 18, 2023

Judahh commented on Jul 18, 2023

klondikemarlen commented on Aug 14, 2026

Current application workaround

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions