Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

trigger to insert the id (or not)

391271May 24 2007 — edited May 24 2007
Hello,

I need a trigger which inserts for me the ID in a table reading a sequence. But also in case an external tool does this (read the sequence before inserting and using that value for the ID), the transaction should not fail.

I try following:

create or replace trigger T_E_ID
before insert on EXA
for each row
begin
if (:new.E_ID is null) then
select S_E_ID.nextval into :new.E_ID from dual;
elsif (:new.E_ID <> S_E_ID.currval) then
raise_application_error(-20001, 'either null for the id or seq.currval');
end if;
end;


the first part is working if I use it alone
if (:new.E_ID is null) then
select S_E_ID.nextval into :new.E_ID from dual;


but the elsif is failing...
why is it saying?:

Error(6,2): PL/SQL: Statement ignored
Error(8,40): PLS-00357: Table,View Or Sequence reference 'S_E_ID.CURRVAL' not allowed in this context


Thanks!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 21 2007
Added on May 24 2007
4 comments
509 views