trigger to insert the id (or not)
391271May 24 2007 — edited May 24 2007Hello,
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!