Oracle auto-inc trigger/sequence not being detected

[Corey]

Hello,

I setup a table with a PK that is populated by a sequence and trigger combination as detailed here. When I create my model, the Identity Mechanism is not set to DatabaseServerCalculated as expected.  I can go in and change it and then it works fine, but if I start working with a larger model, that is going to be a pain.  Is there something I'm missing, or is that feature not working quite right?

create or replace trigger trg_IMP_SALES_HISTORY_inc
            before insert on IMP_SALES_HISTORY
            for each row

begin
    select seq_imp_sales_history.nextval
      into :new.imp_sales_history_id
      from dual;
end;

Oracle 11g, VS2010, .Net 4.0, ORM 2012.1.301.2

Thanks in advance, 
Corey

[Damyan Bogoev]

Hi Corey,

The auto support in Oracle and Firebird using triggers and sequences / generators was introduced in the following internal build of the product – Telerik OpenAccess ORM 2012.1.329.1.

You could download the latest internal build of the product – Telerik OpenAccess ORM 2012.1.427.1, it includes the support for default values and autoinc via triggers in Oracle.

Hope that helps.

Kind regards,
Damyan Bogoev
the Telerik team

[Greg]

What are the exact criteria for detecting a column as autoinc? I have multiple tables with before insert triggers, some of them are detected as autoinc, some of them are not, and I can't seem to spot the difference in the DDL.
I'm using 2013.2.611.1

[Ralph Waldenmaier]

Hi Greg,

We are checking the triggers in the database to determine if a class uses an autoinc mechanism using the following sql script(Assuming that your database is Oracle):

select distinct table_owner, table_name, column_name
from sys.all_trigger_cols t
where column_usage = any('NEW OUT', 'NEW IN OUT')

Can you check if the columns where you expect us to detect the autoinc mechanism are returned with this sql statement? 

What is different in the triggers that are not recognized and those that are? Would it be possible for you to provide us with the trigger code of these tables that are not recognized?

I am looking forward to hearing from you soon and getting this solved with you.
Regards,
Ralph
Telerik

[Greg]

They are indeed not present in the query (had to replace all_trigger_cols with user_trigger_cols because all_trigger_cols timed out). Seems like the column_usage is 'NEW IN OUT OLD IN' for the problematic columns and 'NEW IN OUT' for the good ones. The trigger code is practically identical for both cases, only the table/column/sequence names are different:

 

TRIGGER "SCHEMA"."MYTRIGGERNAME" BEFORE INSERT ON "MYTABLE"
FOR EACH ROW
DECLARE
  v_newVal NUMBER(12) := 0;
  v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.ID IS NULL THEN
    SELECT MYTABLE_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    IF v_newVal = 1 THEN
      SELECT NVL(MAX(ID),0) INTO v_newVal FROM MYTABLE;
      v_newVal := v_newVal + 1;
      LOOP
        EXIT
      WHEN v_incval>=v_newVal;
        SELECT TABLE_SEQ.NEXTVAL INTO v_incval FROM dual;
      END LOOP;
    END IF;
    :new.ID := v_newVal;
  END IF;
END;

Unfortunately the trigger must consider possible pre-existing data and some compatibility problems hence the complexity.
Using Oracle Database 11g Release 11.2.0.1.0

[Ralph Waldenmaier]

Hello Greg,

Thank you for providing your trigger code. Indeed this triggers seems to be valid for an autoinc detection. Therefore I have added this to our backlog and it will be addressed soon.
In the meantime you should be able to work with this setup by manually specifying the respective identity mechanism for your classes.

Again thank you for sharing your trigger code and bringing this to our attention.
Do come back in case you have any other question

Regards,
Ralph
Telerik