Tips and Tricks in a world of Mix

The environment :
Working with Oracle 10g.
At the mapping files of the FNH on the ID column define the sequence to work with.
This is a relevant sequence used by the relevant trigger used for the current table.
The db is a legacy db – using triggers to increment the records on the insert.
The problem :
When I save the record I’m getting back the record number. Well I’m getting the expected number +1 , every time . The record is being inserted and the record number jumps two times instead of one.
The solution:
In the trigger you must enter check to see if the new value is null , and only then increment the trigger . If it’s not null it means it was incremented by FNH already and there is no need to activate the increment in the trigger.

CREATE OR REPLACE TRIGGER DOAR_DEV.CUSTOMER_TRG1
BEFORE INSERT
ON DOAR_DEV.CUSTOMER REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (
NEW.CST_RECID IS NULL
)

DECLARE
tmpVar NUMBER;
BEGIN
tmpVar := 0;

SELECT Customer_SEQ.NEXTVAL INTO tmpVar FROM dual;
:NEW.Cst_RecID := tmpVar;

EXCEPTION
WHEN OTHERS THEN
— Consider logging the error and then re-raise
RAISE;
END ;
/

Comments on: "Fluent NHibernate Sequence Strategy with Oracle" (1)

  1. […] Fluent Nhibernate Trigger-Identity problem 09/11/2010 grekai Leave a comment Go to comments In our case we work with Oracle 10g with FluentNhibernate 1.0.0.597 and Hibernate 2.1.2.4000 . We have a legacy system based on the db used in our new website . It means any change on the db may influence the old system. When we mapped through GeneratedBy.Sequence(“My_Sequence”) , the sequence progressed but it progressed twice – once by NHibernate and once by the trigger on the table. solution : https://grekai.wordpress.com/2010/01/29/fluent-nhibernate-sequence-strategy-with-oracle/ […]

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

Tag Cloud

%d bloggers like this: