Dear Tom,
When having a table with an autogenerated ID like this:
create table ids (
id number generated by default as identity,
t varchar2(300));
And creating a trigger like this:
create or replace trigger ids_trig before insert on ids for each row begin
dbms_output.put_line(:new.id || ' ' || :new.t);
end;
Now adding rows like this:
insert into ids (t) values ('Sometext');
insert into ids (id,t) values (7,'SomeOthertext');I do get the dbms_output like this:
1 Sometext
7 SomeOthertext
I am wondering: is there any possibility to figure out in the triger whether the "id" column was autogenerated or explicitly set in the insert statement?
Thank you for your help!
Best regards,
Philip
Find the name of the sequence Oracle Database created for the identity. Then you can inspect whether its currval matches the new id:
create table ids (
id number
generated by default as identity,
t varchar2(300)
);
select column_name, data_default
from user_tab_cols
where table_name = 'IDS';
COLUMN_NAME DATA_DEFAULT
ID "CHRIS"."ISEQ$$_147783".nextval
T
create or replace trigger ids_trig before
insert on ids
for each row
begin
if :new.id = ISEQ$$_147783.currval then
dbms_output.put_line('SAME');
else
dbms_output.put_line('DIFF');
end if;
end;
/
set serveroutput on
insert into ids ( t ) values ( 'Sometext' );
SAME
1 row created.
insert into ids ( id,t ) values ( 7,'SomeOthertext' );
DIFF
1 row created.Though note this isn't 100% foolproof. If the value you supply happens to match the currval, you'll get a false positive:
insert into ids ( id,t ) values ( 1,'SomeOthertext' );
SAME
1 row created.
Hopefully you have a primary or unique constraint on the identity column though, so at least you'll get an ORA-1 when this happens.
Also be aware that to access currval, you have to initialize it by calling nextval first. So in a new session you might bump into this error:
insert into ids ( id,t ) values ( 1,'SomeOthertext' );
insert into ids ( id,t ) values ( 1,'SomeOthertext' )
*
ERROR at line 1:
ORA-08002: sequence ISEQ$$_147785.CURRVAL is not yet defined in this session
ORA-04088: error during execution of trigger 'CHRIS.IDS_TRIG'So you'll need appropriate exception handling in place.