Problem with triggered Oracle sequences

Classic Servoy
1

Hello,

when I create a new record on a table with a database sequence I get the following problem on an Oracle 8 database.

  • Within the Servoy ‘column info dialog > database sequence’ I told servoy the sequence name of the table: ‘bilder_seq’

  • On the Oracle database there is a trigger which runs after an insert and sets the pk: ‘get_bilder_seq’

  • When I create a new record within Servoy the PK column first gets pk 1000 for example.
    After controller.saveData() I can see the new record in the database; but the pk of that record is 1001; not 1000. At that moment Servoy still things the pk value is 1000.

I did not found any solution within Servoy to fix this problem. If I try to set the pk column to ‘Database default’ (null) will be returned instead of 1000.

Would be nice to have a fix for that problem; now I post a query to get the pk of that new record, set the pk value into a global, get the record via relation and define further values of that record…

Greetings
Christoph

2

Hi Christoph,

What version of the JDBC driver do you use to access that Oracle database ?

3

Christoph,

What I think happens is that both Servoy and the trigger get the next value from the sequence and apply it to the new record.

Can you turn the trigger in oracle off? or change it that it does not set a pk value when there is already one?

Rob

4

A proper trigger in Oracle should only get a new sequence value if the record that gets inserted doesn’t allready contain a value for the field with the sequence attached, as Rob suggests.

Paul

5

Thank you for your tip!
The trigger now returns no value if the pk already contains a value.

Regards
Christoph