[Dbix-class] Oracle AutoPK and Sequencies

[Johnny Gebreselassie]

    Normally you would create an on-insert trigger that fills the id
    column(s) in case none was passed to the query.

    CREATE OR REPLACE TRIGGER yourschema.yourtriggername
        BEFORE INSERT ON yourschema.yourtablename
        FOR EACH ROW WHEN(new.id_column IS NULL) BEGIN
            SELECT yourtriggername.nextval INTO :new.id_column FROM dual;
    END;


I am not a fan of this solution, and this brings up a point/problem that 
I am going to try to fix real soon.  The problem with this solution is 
that for every single table you will need to create a trigger.  I think 
a better solution is to use the sequence defined in the table class, 
override the insert method, select the next value from dual, populate 
the row, then call the super insert.  I'm actually going to try this 
today, my only concern is performance I'll do some primitive benchmarking.

Another benefit of my solution is that it isolates the solution to DBIC, 
which might be beneficial if you have ETL processes that hit these 
tables as well as they will not be executing the trigger code on every 
insert.  I'll be more than happy to share the results of my 
experiment/process if the user list will tolerate it.

Thanks!


Johnny Gebreselassie




Alexander Hartmaier wrote:
> Am Donnerstag, den 11.06.2009, 09:38 +0200 schrieb Doug Scoular:
>   
>>     However, I've recently discovered that people sometimes
>>     manually add records to the database without obtaining
>>     IDs from the sequence. If this happens what is the best
>>     way to obtain the next ID value ? My suspicion is that
>>     my use of sequences becomes invalid.
>>
>>     Can I just ignore the sequence in this case...
>>     I thought I could just select all IDs in order and add one
>>     to the highest one and use that... is this sensible ?
>>     Can DBIC already do this for me ?
>>     
>
> Normally you would create an on-insert trigger that fills the id
> column(s) in case none was passed to the query.
>
> CREATE OR REPLACE TRIGGER yourschema.yourtriggername
>     BEFORE INSERT ON yourschema.yourtablename
>     FOR EACH ROW WHEN(new.id_column IS NULL) BEGIN
>         SELECT yourtriggername.nextval INTO :new.id_column FROM dual;
> END;
>
>   
>>     Any thoughts much appreciated...
>>
>>     Cheers,
>>
>>     Doug
>>     
> --
> BR Alex
>
>
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
> T-Systems Austria GesmbH   Rennweg 97-99, 1030 Wien
> Handelsgericht Wien, FN 79340b
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
> Notice: This e-mail contains information that is confidential and may be privileged.
> If you are not the intended recipient, please notify the sender and then
> delete this e-mail immediately.
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
>