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?
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
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'.
Just to add my solution based on an adaptation of pricerc response above - since crucually I'm using the NuGet PetaPoco version (v6.x) and don't have the source to change (nor the inclination to build it locally as a custom in-house version).
Basically I'm deriving from SqlServerDatabaseProvider so I can customise the behaviour of the SQL (SQL Server T-SQL) that PetaPoco uses (refer here for more info on that topic) and can easily implement a modified solution by overriding the ExecuteInsert and ExecuteInsertAsync methods to customise the IDbCommand command text to suit the use of the @Results table variable approach described.
The specific code additions my custom database provider (derived from SqlServerDatabaseProvider) are:
private string GetInsertPreamble(string primaryKeyName)
{
return string.Format("DECLARE @result TABLE({0} sql_variant); ", primaryKeyName);
}
private string GetInsertOutputClauseWithInsertIntoResult(string primaryKeyName)
{
return string.Format(" OUTPUT INSERTED.[{0}] into @result({0})", primaryKeyName);
}
private string GetInsertPostScript(string primaryKeyName)
{
return string.Format("; SELECT {0} FROM @result; ", primaryKeyName);
}
private void ApplyWorkaroundForSQLError334(string primaryKeyName, ref IDbCommand cmd)
{
StringBuilder newCmdText = new();
if (cmd.CommandText.Contains($" OUTPUT INSERTED.[{primaryKeyName}]", StringComparison.OrdinalIgnoreCase))
{
//Amend the the original PetaPoco INSERT statement to replace the original " OUTPUT INSERTED.XXX" part with one that inserts the "OUTPUT INSERTED.[XXX]" value
//into a @result table variable so the PK value can be selected out at the end as PetPoco expects.
newCmdText.AppendLine(GetInsertPreamble(primaryKeyName));
newCmdText.AppendLine(
cmd.CommandText.Replace(
$" OUTPUT INSERTED.[{primaryKeyName}]",
GetInsertOutputClauseWithInsertIntoResult(primaryKeyName)
)
);
newCmdText.AppendLine(GetInsertPostScript(primaryKeyName));
cmd.CommandText = newCmdText.ToString();
}
}
public override object ExecuteInsert(Database db, IDbCommand cmd, string primaryKeyName)
{
ApplyWorkaroundForSQLError334(primaryKeyName, ref cmd);
//Call the base method to continue with the PetaPoco insert as expected
return base.ExecuteInsert(db, cmd, primaryKeyName);
}
public override Task<object> ExecuteInsertAsync(CancellationToken cancellationToken, Database db, IDbCommand cmd, string primaryKeyName)
{
ApplyWorkaroundForSQLError334(primaryKeyName, ref cmd);
//Call the base method to continue with the PetaPoco insert as expected
return base.ExecuteInsertAsync(cancellationToken, db, cmd, primaryKeyName);
}
Please feel free to suggest improvements or any obvious issues, but it seems to work well thus far in my tests.