Can't insert in table with autoincrement and trigger

[julienadam]

Hi,

On SQL server, I have a table with an autoincrement primary key and a trigger on INSERT.

The insert statement generated by PetaPoco uses OUTPUT to capture the value of the autoincrement but that is unsupported when there's a trigger, see : http://msdn.microsoft.com/en-au/library/ms177564.aspx

Cheers,

Julien

[jcomet]

It looks like the problem started with this change two years ago: https://github.com/toptensoftware/PetaPoco/pull/88

I have this problem as well. It is a fairly simple fix to remove the OUTPUT clause and append ;SELECT SCOPE_IDENTITY(). I wonder why it hasn't been done already. Maybe they don't want the fix. Microsoft is pushing the new OUTPUT method, but SCOPE_IDENTITY() is perfectly fine for single row inserts on.

It was fixed in the schotime NPoco fork a year ago, so you can look there too.

Can one of the committers comment on this?

EDIT: SCOPE_IDENTITY() should be used for identity columns, but not for UNIQUEIDENTIFIER columns. UNIQUEIDENTIFIER columns should use OUTPUT INTO a variable, then return the variable in order to support triggers.

[dazinator]

I would think both should be supported.

For example, suppose when I insert a record, a "createdon" value, and the "Id" is generated on the server, and the "Id" is an auto increment field.

It should be possible to include the "CreatedOn" property in an OUTPUT clause of the insert statement, and have the value mapped back to that property after the insert. Likewise for any other server generated values.

Id is a special case where there happens to be a SCOPE_IDENTITY() function that will return you the ID, but this doesn't help get at the other server generated values which an OUTPUT clause will.

Also my understanding is that an OUTPUT clause will work with triggers as long as you use the INTO syntax?

[pricerc]

One possible solution in http://stackoverflow.com/a/39715357/2258866 (mine), another similar in https://github.com/CollaboratingPlatypus/PetaPoco/issues/201 (which I noticed when I came here to post mine)