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