It's just a shame some of the examples the present show some better practices, even in Apex 4.0.
SELECT MAX(empno) + 1 ?
How many years have we been telling people to never use that in their code?
And if you select "Existing sequence" you get an ugly looking page process with
declare function get_pk return varchar2 is begin for c1 in (select EMP_SEQ.nextval next_val from dual) loop return c1.next_val; end loop; end; begin :P1_EMP_NO := get_pk; end;
As a better alternative, select "Custom PL/SQL function", or if you want to obfuscate it within a trigger you could do it as follows:
CREATE OR REPLACE TRIGGER emp_bi BEFORE INSERT ON sage.emp FOR EACH ROW BEGIN IF :NEW.emp_no IS NULL THEN SELECT emp_seq.NEXTVAL INTO :NEW.emp_no FROM dual; END IF; END; /
With the key line here being 5 - only source from the sequence if the emp_no is not already supplied. If your table is also being populated from another source, it would be more efficient to use
INSERT INTO emp (emp_no, ...) VALUES (emp_seq.NEXTVAL, ...);
I think within the context of most applications in Application Express, where your users are performing data entry at a page level, I don't think it matters if the sequence is populate like this in a page process or trigger.
Also not that in Oracle 11g, in lieu of the implicit cursor you can use
:NEW.emp_no := emp_seq.NEXTVAL
However, last time I ran a trace on this, it was the same as selecting from dual - handy, not a performance improvement.
Any other preferred methods?
Update March 2016
Sven Weller provides the perfect APEX 'trigger'
Hi,
ReplyDeleteyou are right. I have filed a bug to update the examples and the generated code.
Thanks for the hint
Patrick
I always create my own trigger just like your example. Then I don't have to worry about where the INSERTs are coming from. My only problem comes when I copy records from the Test db to Production, but forget to update the Production sequence! Yikes! :-(
ReplyDeleteExcellent tip.