From: Steve Hyde
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
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
answered 07 Nov '10, 10:52