home *** CD-ROM | disk | FTP | other *** search
- Path: sparky!uunet!mcsun!news.funet.fi!hydra!klaava!cc.helsinki.fi!jaakola
- From: jaakola@cc.helsinki.fi
- Newsgroups: comp.databases.oracle
- Subject: Re: Referencing a SEQUENCE from a trigger...
- Message-ID: <1992Jul21.173241.1@cc.helsinki.fi>
- Date: 21 Jul 92 15:32:41 GMT
- References: <1992Jul20.195514.8085@unixg.ubc.ca>
- Sender: news@klaava.Helsinki.FI (Uutis Ankka)
- Followup-To: comp.databases.oracle
- Organization: University of Helsinki
- Lines: 47
-
- In article <1992Jul20.195514.8085@unixg.ubc.ca>, george@unixg.ubc.ca (George chow) writes:
- > I've got a situation where I need to insert rows into a table in a
- > ON-VALIDATE-FIELD trigger. For these rows, I need a serial no which I'm
- You can't execute DML statements (inserts, updates, deletes) from an
- ON-VALIDATE-FIELD trigger. You should think how you could do the job in
- transactional triggers (pre/post-insert/delete/update etc). Or you have
- to do your insert through the base table mechanism.
-
- And using ON-VALIDATE-FIELD trigger is bad choise because it may happen
- that the trigger gets executed many times, each of which grabs another
- sequence number; and the user might rollback any changes, which leaves
- gaps into your serial numbers. The gap problem is inherent with
- sequences, but that's acceptable here, right?
-
- > getting from a SEQUENCE. However, I can't seem to reference the SEQUENCE.
- > I'm doing something like:
- >
- > new_ref := :SEQUENCE.V_REF_NO_SEQ.NEXTVAL;
- Try to rethink this by using transactional triggers. There, if you are
- not inserting via the base table mechanism, you can write:
-
- INSERT INTO the_table (serial_no, ...)
- SELECT v_ref_no_seq.nextval, :block.fieldname, ...
- FROM dual;
-
- Maybe INSERT ... VALUES would do as well, look at the manual. This is
- clean and avoids the PL/SQL variable new_ref. In my opinion, having many
- scratch variables decreases readability and is not in 4GL spirit. This
- solution obviously has the drawback that you have no way of knowing what
- number just got inserted. If you really want to know that, use SELECT
- v_ref_no_seq.nextval INTO new_ref FROM dual. The original assignment
- statement is illegal, because you can reference sequences only in SELECT
- column expressions.
-
- >
- > which gets me the error messages:
- >
- > PL/SQL error 49 at line 10, column 16:
- > bad bind variable 'sequence.v_ref_no_seq'
- >
- > I've gone through the _SQL Language Reference_ and _SQL*Forms Designer
- > Reference_ and I can't find anything useful there. Is the ":SEQUENCE."
- > syntax only valid for use with default value?
- Yes.
- --
- Juhani Jaakola
- jaakola@cc.helsinki.fi
-