The forum is currently being migrated to a new host. While the migration happens, the old server is still available. Once the new machine is ready, I will copy the database to the new machine. Feel free to ask and answer questions in the meantime.

From: Steve Hyde

Newsgroups: sybase.public.sqlanywhere.general

Subject: ASA 10 Autoincrement Trigger Issue

Date: 6 Nov 2010 23:15:25 -0800

I am having a problem managing autoincrement columns in triggers. My technique works in some cases, in others it does not. I'm not sure what the differentiating parameter is, but I do have a sample--

Create a Table and trigger as follows

   XId integer NOT NULL DEFAULT autoincrement,

create trigger tib_X before insert on dba.X
referencing new as n
for each row
  if n.XID = 0 then
    set n.XID=NULL
  end if

Then, then following should produce a new row in the table


Instead it tells me I can't insert a null into column XId, but since XId is autoincrement, I'm not inserting a null into the table.

This works ---

INSERT dba.X (XID) VALUES (null)

(so, hey i can insert a null into XId)

Extra cleanup/debug commands ---

select * from dba.X

drop table dba.X

I am using Version

Obviously, I am trying to manage the case where a client tool does an insert but specified the primary key value as zero rather than null.

asked 07 Nov '10, 10:51

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%


You usually would just omit the column in the insert statement and rely that for omitted columns, the default is taken - in this case the AUTOINCREMENT default. But for this (quite useless) case - a table with only one column - , that obviously won't work:)

(07 Nov '10, 20:55) Volker Barth

Well, this comes as a surprise...

>This works ---
>INSERT dba.X (XID) VALUES (null) seems to be working just like VALUES ( DEFAULT ).

Who knew? :)

Anyway, check out the GET_IDENTITY function:


permanent link

answered 07 Nov '10, 10:52

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

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: 07 Nov '10, 10:51

question was seen: 1,773 times

last updated: 07 Nov '10, 10:52