We are using PetaPoco as our data access tool for a SQL 2008 database. We have a problem when trying to insert/update a row on a table that has a trigger attached.
We are using PetaPoco's db.Save(object);
The error shown is: The target table 'the_table_with_a_trigger' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
How can we use PetaPoco to insert/update data on a table that has a trigger?
Thanks to @Eduardo Molteni, you put me on the right track to solving this. Apparently it is known issue in SQL Server 2008 R2 that the OUTPUT command in an insert will fail if the table has a trigger. However, PetaPoco automatically inserts the OUTPUT clause into the command text of any insert where the table has AutoIncrement=true.
The solution for me, (for SQL Server 2008 R2) is the following:
1) Go to the PetaPoco.DatabaseTypes.SqlServerDatabaseType.GetInsertOutputClause function
remove (comment out)
\\return String.Format(" OUTPUT INSERTED.[{0}]", primaryKeyName);
This removes the "OUTPUT" from the SQL insert statement. Now, the insert will happen on a table with a trigger. However, now PetaPoco has no way to retrieve the new primary key (identity) from the newly inserted row.
2) Go to the PetaPoco.Database.Insert function. Immediately above the line:
object id = _dbType.ExecuteInsert(this, cmd, primaryKeyName);
add a new line, so it looks like this:
cmd.CommandText += ";\nSELECT SCOPE_IDENTITY() AS NewID;";
object id = _dbType.ExecuteInsert(this, cmd, primaryKeyName);
The new line (which existed in PetaPoco, but wasn't being used) will allow the insert statement to retrieve the identity.