How do I SET a column value = the autoincrement default in a trigger?

[Breck_Carter]

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.

[VolkerBarth]

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:)

[Breck_Carter]

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