CREATE TABLE "DBA"."test2" ( "id" BIGINT NOT NULL DEFAULT AUTOINCREMENT, "advancedid" VARCHAR(250) NOT NULL UNIQUE, PRIMARY KEY ( "id" ASC ))
How to achieve, that if an insert is not containing a value for advancedid that the value of id for the inserted row is used and that if the value is provided the value from the insert is used? So no value => use id. If a value is provided use that value.
asked 29 Aug '11, 04:39
I was about to suggest using a DEFAULT AUTOINCREMENT but that doesn't work as soon as you really insert a non-default value - then these two DEFAULT AUTOINCREMENTs will each increase on their own. And using DEFAULT with the @@IDENTITY special value doesn't work, either, as it would get its value before the ID column is set.
So using a before trigger might be your solution:
create trigger TI_test before insert on "DBA".test2 referencing new as N for each row begin if N."advancedid" is null then set N."advancedid" = N."id"; end if; end; -- some test cases insert test2 values(default, default); insert test2 values(default, default); insert test2 values(default, 1000); insert test2 values(default, default); insert test2 values(default, 2000); insert test2 values(100, 200); insert test2 values(101, default); select * from test2; -- output 1 1 2 2 3 1000 4 4 5 2000 100 200 101 101