[Dbix-class] Oracle AutoPK and Sequencies

[Alexander Hartmaier]

Am Montag, den 15.06.2009, 15:49 +0200 schrieb 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.

This solution is for plain Oracle, without DBIC!
There is no other way in Oracle to accomplish auto-id-generation.

You don't need to override the insert method, thats what PK::Auto is
for!
As soon as you specify a sequence name and your storage is Oracle, DBIC
fetches the next sequence value and uses that for the insert.
If that doesn't work for you debug it.

> 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
--
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.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*