The Short Story
If you have seen this error, whist using NHibernate with Triggers, this may be the right post for you
“Batch update returned unexpected row count from update; actual row count: 2; expected: 1″.
Long Story
So today I had to create some audit tables, ok some of you will surely go hey why didn’t you just use CQRS for that? Yes yes I know that by using CQRS and event sourcing I would indeed get full audit by way of the stored events, in fact I will be writing about that soon, but for now lets forget about that and just stick to the current situation which is :
- I have a table that I want to provide auditing for
- I am using SQL Server
- I am using Fluent NHibernate
Now there are numerous ways you may perform auditing in SQL server, in fact later versions of SQL Server come with inbuilt Audit functionality, or you could use a general all purpose audit table which records very generic information such as
- The table name
- The action performed (insert, update, delete)
- The old value
- The new value
- Some narrative
- Some date information
This is fine, but for my requirements, what I wanted was a full row copy from the original table, plus some extra columns such as
- AuditId (Primary key auto generated by DB (Identity))
- OperationType : I for Insert, D for delete, U for update
So lets have a look at some of this using some code example
SQL Server Table(s)
Let say I have the following table called “Deposit” in SQL Server
Deposit Table (source for Audit)
CREATE TABLE [dbo].[Deposit](
[Id] [int] IDENTITY(1,1) NOT NULL,
….
….
….
[Version] [timestamp] NOT NULL
CONSTRAINT [PK_Deposit] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
And I also have the following Audit table
Deposit_Audit table
CREATE TABLE [dbo].[Deposit_Audit](
[AuditId] [int] IDENTITY(1,1) NOT NULL,
[OperationType] [nvarchar](1) NOT NULL,
[Id] [int] NOT NULL,
….
….
….
….
CONSTRAINT [PK_Deposit_Audit] PRIMARY KEY CLUSTERED
(
[AuditId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
The Fluent NHibernate Entities
So lets say I have a simple class called “Deposit” which looks like this. Note that for Fluent NHibernate/NHiberate to do their magic properties MUST be virtual
using System;
namespace Entities
{
public class Deposit : Entity<int>
{
//NOTE : Do not remove this, as NHibernate needs default ctor to allow proxying
public Deposit() { }
public virtual int Id { get; set; }
….
….
….
….
public virtual byte[] Version { get; set; }
}
}
The Fluent NHibernate Mapping Files
Where I then have a Fluent NHibernate mapping file that looks like this
namespace Entities
{
public class DepositMap : ClassMap<Deposit>
{
public DepositMap()
{
this.Schema("[dbo]");
OptimisticLock.Version();
this.Table("[Deposit]");
this.Id(x => x.Id).Column("Id");
….
….
….
Version(x => x.Version).Generated.Always();
}
}
}
The Trigger To Do The Audit
Then I have this trigger in place in SQL server to actually do the inserting of the data into the audit table
CREATE TRIGGER [dbo].[triDeposit_Audit]
ON [dbo].[Deposit]
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
DECLARE @rc AS INT;
DECLARE @Id INT
DECLARE @OperationType NVARCHAR(1)
DECLARE @rcDel AS INT;
DECLARE @rcInserted AS INT;
SET @rc = (SELECT COUNT(*) FROM inserted);
IF @rc = 0 RETURN;
DECLARE @keycol AS INT;
SELECT
@keycol = ISNULL(inserted.Id, deleted.Id),
@OperationType =
CASE
WHEN inserted.Id IS NULL THEN 'D' - we don't use this for now
WHEN deleted.Id IS NULL THEN 'I'
ELSE 'U'
END
FROM inserted
FULL OUTER JOIN deleted
ON inserted.Id = deleted.Id
- single row
IF @rc = 1
BEGIN
INSERT INTO [Options].[dbo].[Deposit_Audit]
( [OperationType]
,[Id]
…..
…..
…..
…..
)
SELECT
@OperationType
….
….
….
….
FROM [Options].[dbo].[Deposit] WHERE Id = @keycol
END
- multiple rows
ELSE
BEGIN
SELECT DISTINCT * INTO #I FROM inserted;
BEGIN
INSERT INTO [Options].[dbo].[Deposit_Audit]
(
[OperationType]
,[Id]
……
……
……
)
SELECT
'U'
,[Id]
……
……
……
FROM #I
END
END
END
GO
All good so far, so I then used my handy NHibernate ISession / Repository to do an INSERT, and then I got something that I was not expecting, I got this:
In NHibernate I got this error: “Batch update returned unexpected row count from update; actual row count: 2; expected: 1″.
Yikes.
Turns out there is a simple fix for this, which to my mind was not obvious (even though I seem to recall seeing this before, and mindfully forgetting about it), you just need to include the following line at the start of your SQL Server trigger
SET NOCOUNT ON
By doing that you are saying that the code that runs inside the trigger will not effect the overall row count of the transaction. After adding this one line to my trigger everything worked as expected.
Sacha's Blog 