Oracle auto-inc trigger/sequence not being detected

6 posts, 0 answers
  1. Corey
    Corey avatar
    6 posts
    Member since:
    Jun 2012

    Posted 05 Jun 2012 Link to this post

    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
  2. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 06 Jun 2012 Link to this post

    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
    Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
  3. DevCraft banner
  4. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 01 Aug 2013 Link to this post

    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
  5. Ralph Waldenmaier
    Admin
    Ralph Waldenmaier avatar
    202 posts

    Posted 01 Aug 2013 Link to this post

    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
    OpenAccess ORM Q2 2013 brings you a more powerful code generation and a unique Bulk Operations support with LINQ syntax. Check out the list of new functionality and improvements shipped with this release.
  6. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 01 Aug 2013 Link to this post

    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

  7. Ralph Waldenmaier
    Admin
    Ralph Waldenmaier avatar
    202 posts

    Posted 02 Aug 2013 Link to this post

    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
    OpenAccess ORM Q2 2013 brings you a more powerful code generation and a unique Bulk Operations support with LINQ syntax. Check out the list of new functionality and improvements shipped with this release.
Back to Top
DevCraft banner