PetaPoco insert fails on table with trigger

Question

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?

Answer 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.

Answer 2

Because I'm sure I won't be the last person who encounters this...

I've adopted PetaPoco for new project, but I ran into a similar problem, but reverting to scope_identity() wasn't going to work. So I:

1) Extended the IProvider interface.

    /// <summary>
    ///     Return an SQL expression that can be used with <seealso cref="GetInsertPostScript(string)"/>
    ///     and <seealso cref="GetInsertOutputClause(string)"/> to return a provider-generated value from an INSERT; typically an IDENTITY 
    ///     column in Microsoft SQL Server.
    /// </summary>
    /// <param name="primaryKeyName"></param>
    /// <returns></returns>
    string GetInsertPreamble(string primaryKeyName);

    /// <summary>
    ///     Return an SQL expression that can be used with <seealso cref="GetInsertPreamble(string)"/>
    ///     and <seealso cref="GetInsertOutputClause(string)"/> to return a provider-generated value from an INSERT; typically an IDENTITY 
    ///     column in Microsoft SQL Server.
    /// </summary>
    /// <param name="primaryKeyName"></param>
    /// <returns></returns>
    string GetInsertPostScript(string primaryKeyName);

2) Added them to DatabaseProvider.cs:

    public virtual string GetInsertPreamble(string primaryKeyName)
    {
        return string.Empty;
    }

    public virtual string GetInsertPostScript(string primaryKeyName)
    {
        return string.Empty;
    }

3) Then SqlServerDatabaseProvider, including changing the existing OUTPUT clause:

    public override string GetInsertOutputClause(string primaryKeyName)
    {
        return String.Format(" OUTPUT INSERTED.[{0}] into @result({0})", primaryKeyName);
    }

    public override string GetInsertPreamble(string primaryKeyName)
    {
        return string.Format("DECLARE @result TABLE({0} sql_variant); ", primaryKeyName);
    }

    public override string GetInsertPostScript(string primaryKeyName)
    {
        return string.Format("; SELECT {0} FROM @result; ", primaryKeyName);
    }

4) Lastly, incorporated these into Database.cs:

                    ...string outputClause = string.Empty;
                    string insertPreamble = string.Empty;
                    string insertPostScript = string.Empty;
                    if (autoIncrement)
                    {
                        insertPreamble = _provider.GetInsertPreamble(primaryKeyName, tableName);
                        outputClause = _provider.GetInsertOutputClause(primaryKeyName);
                        insertPostScript = _provider.GetInsertPostScript(primaryKeyName, tableName);
                    }

                    cmd.CommandText = string.Concat(
                        $"{insertPreamble}",
                        $"INSERT INTO {_provider.EscapeTableName(tableName)} ({(string.Join(",", names.ToArray()))})",
                        $"{outputClause}",
                        $" VALUES ({(string.Join(",", values.ToArray()))})",
                        $"{insertPostScript}"
                    ) ;

                    if (!autoIncrement)
                    {....

This changes the command to use a named TABLE variable, DECLAREd in the 'Preamble', populated by the OUTPUT clause and SELECTed in the 'PostScript'.

Answer 3

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.

Answer 4

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