Triggers/Rowcount And NHibernate

 

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 :

 

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

Advertisements

One thought on “Triggers/Rowcount And NHibernate

  1. Too cool to be true! How easy it is and satisfying, instead of 10 calls of a client and 5 days diving in sql.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: