Oracle sequence not correct

Posts   
 
    
Puromtec
User
Posts: 76
Joined: 22-Jun-2005
# 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 avatar
Posts: 341
Joined: 30-Nov-2003
# 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 avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-May-2006 10:34:08   

Check if you have an insert trigger on the table.

Frans Bouma | Lead developer LLBLGen Pro
Puromtec
User
Posts: 76
Joined: 22-Jun-2005
# 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 avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# 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"

Frans Bouma | Lead developer LLBLGen Pro
Puromtec
User
Posts: 76
Joined: 22-Jun-2005
# Posted on: 10-May-2006 15:50:36   

Thanks, that works.

like2175
User
Posts: 83
Joined: 27-Mar-2006
# 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.

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 avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 15-May-2006 21:39:51   

GREAT tip! simple_smile

Frans Bouma | Lead developer LLBLGen Pro