[Dbix-class] Oracle AutoPK and Sequencies

[Alexander Hartmaier]

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