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.