CREATE TABLE "DBA"."test2" (
"advancedid" VARCHAR(250) NOT NULL UNIQUE,

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

Martin's gravatar image

accept rate: 14%

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. (I can't prefix the two at signs to the IDENTIY name, as it automatically build a block quote, FWIW... - EDIT: but now it works, thanks to Reimer!)


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
   if N."advancedid" is null then
      set N."advancedid" = N."id";
   end if;

-- 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
permanent link

answered 29 Aug '11, 06:22

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

edited 01 Sep '11, 03:48

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 29 Aug '11, 04:39

question was seen: 703 times

last updated: 01 Sep '11, 03:49