Re: Referencing a SEQUENCE from a trigger...

In article <1992Jul20.195514.8085_at_unixg.ubc.ca>, george_at_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_at_cc.helsinki.fi