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. *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"* _______________________________________________ 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/[email protected]
