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. |
|
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 Displaying @@IDENTITY works with preceding backslashes (\@\@)
(30 Aug '11, 08:34)
Reimer Pods
Replies hidden
It does in a @comment, but (at least for me) it does not work in an answer (which I had tried of course) - the answer editor immediately creates a blockquote once I enter the @sign - no matter if I try to escape it or not:(
(30 Aug '11, 09:35)
Volker Barth
Copying the string "@@IDENTITY" from a lokal editor into the answer text box did the trick for me ;-)
(31 Aug '11, 09:14)
Reimer Pods
@Reimer: Thanks for the pointer:)
(01 Sep '11, 03:49)
Volker Barth
|
