Do either of the following:
1)
CREATE OR REPLACE TRIGGER label_id_bri
BEFORE INSERT ON labels
FOR EACH ROW
BEGIN
IF :new.label_id IS NULL
THEN
SELECT label_id_seq.nextval
INTO :new.label_id
FROM DUAL;
END IF;
END;
-or-
2)
CREATE OR REPLACE TRIGGER label_id_bri
BEFORE INSERT ON labels
FOR EACH ROW
WHEN (new.label_id IS NULL)
BEGIN
SELECT label_id_seq.nextval
INTO :new.label_id
FROM DUAL;
END;
|-----Original Message-----
|From: O'Reilly John [mailto:[EMAIL PROTECTED]]
|Sent: Friday, February 21, 2003 10:15 AM
|To: 'OJB Users List'
|Subject: RE: Oracle sequences/triggers
|
|
|The sequence and trigger are defined as follows:
|
|CREATE SEQUENCE label_id_seq
| INCREMENT BY 1
| START WITH 1
| MINVALUE 1
| MAXVALUE 999999999999999999999999999
| NOCYCLE
| NOORDER
| NOCACHE
|/
|
|CREATE OR REPLACE TRIGGER label_id_bri
|BEFORE
| INSERT
| ON labels
|REFERENCING NEW AS NEW OLD AS OLD
| FOR EACH ROW
|BEGIN
| SELECT label_id_seq.nextval
| INTO :NEW.LABEL_ID
| FROM DUAL;
|END;
|
|-----Original Message-----
|From: Ebersole, Steven [mailto:[EMAIL PROTECTED]]
|Sent: 21 February 2003 16:09
|To: 'OJB Users List'
|Subject: RE: Oracle sequences/triggers
|
|
|If its an actual TRIGGER, then just do a conditional to
|see if the id is
|already set before you grab it from the sequence...
|
|If its a DEFAULT attribute, then I don't know how you
|would get around it.
|
|
|
| |-----Original Message-----
| |From: O'Reilly John [mailto:[EMAIL PROTECTED]]
| |Sent: Friday, February 21, 2003 9:55 AM
| |To: 'OJB Users List'
| |Subject: RE: Oracle sequences/triggers
| |
| |
| |I could drop the trigger and it would probably work.
| |However, I need the
| |trigger in place as the data can be loaded from other sources.
| |
| |
| |-----Original Message-----
| |From: Andrew Gilbert [mailto:[EMAIL PROTECTED]]
| |Sent: 21 February 2003 15:51
| |To: OJB Users List
| |Subject: RE: Oracle sequences/triggers
| |
| |
| |Are you mixing strategies? Drop the trigger in case 2,
| |does it work?
| |
| |
| |-----Original Message-----
| |From: O'Reilly John [mailto:[EMAIL PROTECTED]]
| |Sent: Friday, February 21, 2003 10:23 AM
| |To: '[EMAIL PROTECTED]'
| |Subject: Oracle sequences/triggers
| |
| |
| |
| |Last November a message was posted regarding the use of
| |Oracle Sequences. I
| |was wondering if somone was able to get this working.
| |http://archives.apache.org/eyebrowse/ReadMsg?listName=ojb-u
| |[EMAIL PROTECTED]
| |e.org&msgId=528894
| |
| |As posted, I have defined a trigger based oracle sequence
| |such that the DB
| |will generate a new primary key when inserting a record.
| |I've tried two
| |approaches with OJB.
| |
| |
| |1. Don't define a sequence and define primary key
|field as follows:
| |
| | <field-descriptor name="label_Id" column="LABEL_ID"
| |jdbc-type="INTEGER"
| | primarykey="true"/>
| |
| |
| |In this case the following sql is generated when I create
| |an object. The
| |primary key gets created by the DB. However, OJB doesn't
| |know what this is
| |and therefore cannot create foreign keys correctly.
| |
| |SELECT LABEL_ID,RECORD_COMPANY_ID,LABEL_NAME FROM labels
| |WHERE LABEL_ID =
| |'0'
| |INSERT INTO labels (LABEL_ID,LABEL_NAME,RECORD_COMPANY_ID)
| |VALUES ( '0',
| |'some label', '11' )
| |
| |
| |
| |2. Define a sequence manager
| |
| | <sequence-manager
| |className="org.apache.ojb.broker.util.sequence.SequenceMana
| |gerNextValImpl"/>
| |
| |and define primary key as follows:
| |
| | <field-descriptor name="label_Id" column="LABEL_ID"
| |jdbc-type="INTEGER"
| | primarykey="true"
| | autoincrement="true"
| |
|sequence-name="label_id_seq"
| | />
| |
| |In this case the following sql is generated when I create
| |an object.
| |
| |select label_id_seq.nextval from dual
| |SELECT LABEL_ID,RECORD_COMPANY_ID,LABEL_NAME FROM labels
| |WHERE LABEL_ID =
| |'664'
| |INSERT INTO labels (LABEL_ID,LABEL_NAME,RECORD_COMPANY_ID)
| |VALUES ( '664',
| |'some label', '11' )
| |
| |The problem is that the id created in the DB is actaully
| |665 because of the
| |trigger on the insert. Again, the foreign key would
|be incorrect.
| |
| |
| |Are there any other solutions?
| |
| |We are in the processing of evaluating a move from Castor
| |to OJB. In castor
| |this is handled using the following sql:
| |
| |{call INSERT INTO "SALES"
| |("SALES_YEAR","SALES_QUARTER","RECORD_COMPANY_ID")
| |VALUES ('1999','1','') RETURNING "SALES_ID" INTO null}
| |
| |
| |BTW, some of the posts refer to an Oracle specific
| |sequencemanager -
| |however, I can't find this in the latest distribution (0.9.9).
| |
| |Regards,
| |John O'Reilly
| |
| |
| |***********************************************************
| |*************
| |This e-mail and any files transmitted with it are
| |confidential and may be
| |privileged and are intended solely for the individual
| |named/ for the use of
| |the individual or entity to whom they are addressed.If you
| |are not the
| |intended addressee, you should not disseminate, distribute
| |or copy this
| |e-mail.Please notify the sender immediately if you have
| |received this e-mail
| |by mistake and delete this e-mail from your system.If you
| |are not the
| |intended recipient, you are notified that reviewing,
| |disclosing, copying,
| |distributing or taking any action in reliance on the
| |contents of this e-mail
| |is strictly prohibited.Please note that any views or
| |opinions expressed in
| |this e-mail are solely those of the author and do not
| |necessarily represent
| |those of Traventec Limited.E-mail transmission cannot be
| |guaranteed to be
| |secure or error-free as information could be intercepted,
| |corrupted, lost,
| |destroyed, or arrive late or incomplete.Traventec Limited
| |therefore does not
| |accept liability for any errors or omissions in the
| |contents of this
| |message, which arise as a result of e-mail
| |transmission.The recipient should
| |check this e-mail and any attachments for the presence of
| |viruses.This
| |e-mail has been swept for computer viruses however
| |Traventec Limited accepts
| |no liability for any damage caused by any virus
| |transmitted by this e-mail.
| |
| |-----------------------------------------------------------
| |----------
| |To unsubscribe, e-mail: [EMAIL PROTECTED]
| |For additional commands, e-mail: [EMAIL PROTECTED]
| |
| |
| |-----------------------------------------------------------
| |----------
| |To unsubscribe, e-mail: [EMAIL PROTECTED]
| |For additional commands, e-mail: [EMAIL PROTECTED]
| |***********************************************************
| |*************
| |This e-mail and any files transmitted with it are
| |confidential and may be
| |privileged and are intended solely for the individual
| |named/ for the use of
| |the individual or entity to whom they are addressed.If you
| |are not the
| |intended addressee, you should not disseminate, distribute
| |or copy this
| |e-mail.Please notify the sender immediately if you have
| |received this e-mail
| |by mistake and delete this e-mail from your system.If you
| |are not the
| |intended recipient, you are notified that reviewing,
| |disclosing, copying,
| |distributing or taking any action in reliance on the
| |contents of this e-mail
| |is strictly prohibited.Please note that any views or
| |opinions expressed in
| |this e-mail are solely those of the author and do not
| |necessarily represent
| |those of Traventec Limited.E-mail transmission cannot be
| |guaranteed to be
| |secure or error-free as information could be intercepted,
| |corrupted, lost,
| |destroyed, or arrive late or incomplete.Traventec Limited
| |therefore does not
| |accept liability for any errors or omissions in the
| |contents of this
| |message, which arise as a result of e-mail
| |transmission.The recipient should
| |check this e-mail and any attachments for the presence of
| |viruses.This
| |e-mail has been swept for computer viruses however
| |Traventec Limited accepts
| |no liability for any damage caused by any virus
| |transmitted by this e-mail.
| |
| |-----------------------------------------------------------
| |----------
| |To unsubscribe, e-mail: [EMAIL PROTECTED]
| |For additional commands, e-mail: [EMAIL PROTECTED]
| |
|
|-----------------------------------------------------------
|----------
|To unsubscribe, e-mail: [EMAIL PROTECTED]
|For additional commands, e-mail: [EMAIL PROTECTED]
|***********************************************************
|*************
|This e-mail and any files transmitted with it are
|confidential and may be
|privileged and are intended solely for the individual
|named/ for the use of
|the individual or entity to whom they are addressed.If you
|are not the
|intended addressee, you should not disseminate, distribute
|or copy this
|e-mail.Please notify the sender immediately if you have
|received this e-mail
|by mistake and delete this e-mail from your system.If you
|are not the
|intended recipient, you are notified that reviewing,
|disclosing, copying,
|distributing or taking any action in reliance on the
|contents of this e-mail
|is strictly prohibited.Please note that any views or
|opinions expressed in
|this e-mail are solely those of the author and do not
|necessarily represent
|those of Traventec Limited.E-mail transmission cannot be
|guaranteed to be
|secure or error-free as information could be intercepted,
|corrupted, lost,
|destroyed, or arrive late or incomplete.Traventec Limited
|therefore does not
|accept liability for any errors or omissions in the
|contents of this
|message, which arise as a result of e-mail
|transmission.The recipient should
|check this e-mail and any attachments for the presence of
|viruses.This
|e-mail has been swept for computer viruses however
|Traventec Limited accepts
|no liability for any damage caused by any virus
|transmitted by this e-mail.
|
|-----------------------------------------------------------
|----------
|To unsubscribe, e-mail: [EMAIL PROTECTED]
|For additional commands, e-mail: [EMAIL PROTECTED]
|
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
