Insert into Table with Active Trigger

[nyabutid]

{
  "error": {
    "name": "RequestError",
    "status": 500,
    "message": "The target table 'dbo.tableName' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.",
    "code": "EREQUEST",
    "precedingErrors": [],
    "stack": "RequestError: The target table 'dbo.tableName' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.\n    at Connection.<anonymous> (app/node_modules/loopback-connector-mssql/node_modules/mssql/lib/tedious.js:608:17)\n    at Connection.emit (events.js:95:17)\n    at Parser.<anonymous> (app/node_modules/loopback-connector-mssql/node_modules/mssql/node_modules/tedious/lib/connection.js:450:15)\n    at Parser.emit (events.js:95:17)\n    at Parser.nextToken (app/node_modules/loopback-connector-mssql/node_modules/mssql/node_modules/tedious/lib/token/token-stream-parser.js:103:18)\n    at Parser.addBuffer (app/node_modules/loopback-connector-mssql/node_modules/mssql/node_modules/tedious/lib/token/token-stream-parser.js:80:17)\n    at Connection.sendDataToTokenStreamParser (app/node_modules/loopback-connector-mssql/node_modules/mssql/node_modules/tedious/lib/connection.js:884:35)\n    at Connection.STATE.SENT_CLIENT_REQUEST.events.data (app/node_modules/loopback-connector-mssql/node_modules/mssql/node_modules/tedious/lib/connection.js:289:23)\n    at Connection.dispatchEvent (app/node_modules/loopback-connector-mssql/node_modules/mssql/node_modules/tedious/lib/connection.js:748:59)\n    at MessageIO.<anonymous> (app/node_modules/loopback-connector-mssql/node_modules/mssql/node_modules/tedious/lib/connection.js:676:22)"
  }
}

--I imagine the query used is similar to
INSERT INTO dbo.tableName
        ( ID ,
          Col1 ,
          Col2 
        )
        OUTPUT INSERTED.ID
VALUES  ( 0 ,
          'Col1' ,
          'Col2'
        )
--Microsoft documentation on OUTPUT with TRIGGER enabled: http://msdn.microsoft.com/en-us/library/ms177564.aspx

[nyabutid]

Current workaround:

Replace

// node_modules/loopback-connector-mssql/lib/mssql.js:157
var sql = "INSERT INTO " + tblName + " (" + fieldsAndData.fields + ")" + MsSQL.newline;
  sql += "OUTPUT INSERTED." + modelPKID + " AS insertId" + MsSQL.newline;
  sql += "VALUES (" + fieldsAndData.paramPlaceholders + ");";

with (use select scope_identity() instead of OUTPUT INSERTED)

// node_modules/loopback-connector-mssql/lib/mssql.js:157
var sql = "INSERT INTO " + tblName + " (" + fieldsAndData.fields + ")" + MsSQL.newline;
  sql += MsSQL.newline;
  sql += "VALUES (" + fieldsAndData.paramPlaceholders + ");SELECT SCOPE_IDENTITY() AS insertId;";

[raymondfeng]

What's going to happen if the PK is not an identity, for example, some sort of uuid?

[nyabutid]

In my schema the IDENTITY is always the ID. Can this be provided as a configuration option instead to handle these caveats: when triggers are enabled on a table or when your PK is not the IDENTITY on the table? Or can you extend the SCOPE_IDENTITY to return the PK by selecting the row with that ID after an insert (I'd be concerned about performance on this option)?

[idoshamun]

+1

[tuomastanner]

+1 Switched to @idosh s fork for the time being and all seems to work perfectly. Big thumbs up!

[SandeshSarfare]

Hello All,

I have the same issue however mssql.js which I have seems to be completely different and I am not sure how resolve error "The target table 'dbo.Test' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause".......

For an instance I've below code in my mssql.js where Output is inserted

MsSQL.prototype.buildInsertInto = function(model, fields, options,callback) {
var stmt = this.invokeSuper('buildInsertInto', model, fields, options);
var idName = this.idName(model);
if (idName) {
stmt.merge(MsSQL.newline + 'OUTPUT INSERTED.' +
this.columnEscaped(model, idName) + ' AS insertId');
}
return stmt;
};

[SandeshSarfare]

mssql.js which i have is different than one in this repo.....please suggest a workaround......otherwise we can not go live on production......Thanks in advance

[FoysalOsmany]

@raymondfeng Can you do the code review and merge?

#70

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale]

This issue has been closed due to continued inactivity. Thank you for your understanding. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository.