PetaPoco insert fails on table with trigger

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?

Solution 1:

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.

Solution 2:

I think cmd.CommandText += ";\nSELECT SCOPE_IDENTITY() AS NewID;"; would be better. @@IDENTITY can give you an id generated in the trigger, not your statement.

Solution 3:

PetaPoco only creates an OUTPUT parameter in Oracle DBs to get the new AutoIncrement ID.

In the case of Sql Server, as you tagged your question, it only adds a SELECT SCOPE_IDENTITY() AS NewID if the table has an AutoIncrement ID.

Relevant code in PetaPoco.cs:

cmd.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})",
        EscapeTableName(tableName),
        string.Join(",", names.ToArray()),
        string.Join(",", values.ToArray())
        );

if (!autoIncrement) {
    DoPreExecute(cmd);
    cmd.ExecuteNonQuery();
    OnExecutedCommand(cmd);
    return true;
}

object id;
switch (_dbType) {
    case DBType.SqlServer:
        cmd.CommandText += ";\nSELECT SCOPE_IDENTITY() AS NewID;";
        DoPreExecute(cmd);
        id = cmd.ExecuteScalar();
        OnExecutedCommand(cmd);
        break;

Try turning off the AutoIncrement ID and setting it up manually to see if the problem goes away