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

CREATE TABLE dba.X (
   XId integer NOT NULL DEFAULT autoincrement,
   PRIMARY KEY (XId)  )
go

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

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

INSERT dba.X (XID) VALUES (0)

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 10.0.1.3976.

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
25.7k428592852
accept rate: 20%

1

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)

...it seems to be working just like VALUES ( DEFAULT ).

Who knew? :)

Anyway, check out the GET_IDENTITY function: http://dcx.sybase.com/index.html#html/dbrfen10/rf-get-identity-function.html

Breck

permanent link

answered 07 Nov '10, 10:52

Breck%20Carter's gravatar image

Breck Carter
25.7k428592852
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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×102

question asked: 07 Nov '10, 10:51

question was seen: 1,158 times

last updated: 07 Nov '10, 10:52