Triggers/Rowcount And NHibernate

The Short Story

If you have seen this error, whilst 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, let's forget about that and just stick to the current situation which is:

1. I have a table that I want to provide auditing for
2. I am using SQL Server
3. 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, let's have a look at some of this using some code example.

SQL Server Table(s)

Let's 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 let's 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.