Forum:  Generated code

Thread:  Oracle sequence not correct


Puromtec (User)   Posted on: 09-May-2006 20:33:07.
I am using Oracle 10g and have a problem with the sequence values. When I save an entity (using self-servicing) it obtains the correct "next" sequence value inside the entity object. However, on the database side, the table actually jumps +2. So I get errors following this, since there is no record of the proper sequence in c# side.

Thanks,
Darren
sparmar2000 (User)   Posted on: 10-May-2006 03:54:39.
This is very strange. What happens if you insert a row in the same table using SQL. Does it still jump +2?

What verson of LLBLGen Pro are you using.


Otis (LLBLGen Pro Team)   Posted on: 10-May-2006 10:34:08.
Check if you have an insert trigger on the table.
Puromtec (User)   Posted on: 10-May-2006 14:40:55.
First, thanks for responding.....

1. The database column auto increments properly when I do a manual insert from ISQL


2. I have an "insert before" trigger on the table. I have dropped the table and recreated it with the trigger (there are only two triggers in the schema, one for another table)

I will test this by deleting the other trigger. This is a new project/database, so there aren't many tables at this point.


...and version of llbl gen pro:

1.0.2005.1 Final
Released March 31st, 2006


Otis (LLBLGen Pro Team)   Posted on: 10-May-2006 15:31:32.
It's definitely the trigger. It will increase the sequence with one and because the llblgen pro query will also increase the sequence with one, you'll get an increase of 2.

If you can't drop the triggers, because they're needed, you can tell llblgen pro not to use sequences with nextval, by using a setting in the config file of your application. Please see the documentation on this: "Application configuration through config files"
Puromtec (User)   Posted on: 10-May-2006 15:50:36.
Thanks, that works.

like2175 (User)   Posted on: 15-May-2006 15:48:09.
I have oracle triggers in place to "catch" situations where I may be bulk filling the database without using the vb.net application.

The trigger only inserts a sequence number if it would otherwise be null. In this way will it will happily coexist with LLBLGenPro.

Code:
CREATE OR REPLACE TRIGGER session_error_id BEFORE INSERT
On if.app_session_error
FOR EACH ROW

BEGIN
    IF :NEW.app_session_error_id IS NULL THEN
     SELECT app_session_error_seq.NEXTVAL
        INTO :NEW.app_session_error_id
     FROM DUAL;
    END IF;
END;

Otis (LLBLGen Pro Team)   Posted on: 15-May-2006 21:39:51.
GREAT tip! Regular Smiley