Hibernate and database triggers

Apart from being required for legal and regulatory reasons, audit logging is a very important feature of every good-quality information system. Initially, it always seems like a simple thing to do but, as the projects grow larger, audit logging often becomes an encumbrance for the software developers.

When it comes to the Spring/Hibernate-based applications, there are several solutions considered to be the best practices – Hibernate Interceptor Approach, Envers, database triggers, etc.

While working on a recent project for our major client, we had to review auditing approach in order to improve performance of some critical functionalities and to achieve requested SLA. After evaluating possible solutions and deliberating between the performance impact and ease of implementation, the decision was made to put the audit logging logic into the database triggers. The basic idea is – when a record in the target table is updated or inserted, the appropriate database triggers write necessary auditing information into separate audit tables.

It seemed like a clean and intelligent way to go, especially performance-wise. At least until we did the implementation 🙂

First unit tests showed expected results – auditing worked seamlessly and fast. The problems showed up when we run the complete application for the first time. Suddenly, Hibernate-related exceptions, specifically TooManyRowsAffectedException, were thrown all over the application.

By reviewing the stack trace, we have identified the following piece of code as a source of the exceptions:

if (expectedRowCount < rowCount) {
  String msg = "Unexpected row count: " + rowCount + "; expected: " + expectedRowCount;
  throw new TooManyRowsAffectedException(msg, expectedRowCount, rowCount);
}

This means that an exception is thrown if the rowCount returned by the statement, which represents the number of database rows affected by that statement, is greater than the expected one. The expected row count for the Update operation is 1 by default, which is what the database is supposed to return when updating a single row identified by its primary key.

After introducing the auditing triggers, the database was no longer returning 1 as the result, because the rows inserted into the audit tables by those triggers are also counted among the affected rows. And the results greater than 1 were causing those exceptions to be thrown.

In order to overcome the issue, we had to turn off the count report for the trigger inserts which can be accomplished by the following Microsoft SQLServer command:

SET NOCOUNT ON

There is a similar statement for the Oracle database, which accomplish the same behavior:

SET FEEDBACK OFF

After modifying the trigger to include the above command, database was returning only one result when updating a single row, and our application was running smoothly and with the full audit logging.

Tech Team

Author: Tech Team

When a couple of our Devs and TLs come together magic happens!

Leave a Reply

Your email address will not be published. Required fields are marked *