Hi ,

I have used the following query in sybase to auto generate the primary key.
CREATE TABLE sample_details (
user_id numeric(10,0) identity,
login_name text not null,
password text not null,
group_id integer not null,
primary key(user_id),
foreign key(group_id)
references sample_groups(group_id)
on delete cascade
);

Now i want to know if at all i need to have any trigger for before insertion.

In Oracle,the query was like below :
CREATE TABLE sample_details (
user_id integer not null,
login_name text not null,
password text not null,
group_id integer not null,
primary key(user_id),
foreign key(group_id)
references sample_groups(group_id)
on delete cascade
);

and there was one sequence and trigger cretaed like this:
CREATE SEQUENCE sample_details_user_id increment by 1 start with 1;
CREATE TRIGGER u_d_u_id
BEFORE INSERT
on sample_details
FOR EACH ROW
DECLARE Id integer;
BEGIN
SELECT sample_details_user_id.nextval into Id from dual;
:new.user_id:=Id;
END;
/

Please let me know if the sybase equivalent i had mentioned above would take care of what i have mentioned in Oracle version.Or do is there any necessity to write a trigger?If yes,could u pls let me know the trigger code.

Any immediate help on this is higly appreciated.

Hi,
There is no need to define trigger in sybase. Identity column will increment the value one by one for each insertion into the table. But, sometimes this value can jump when server restarts from the last identity number before server crash.

Thanks a lot.
But could u pls let me know if the increment jump problem can be avoided by using identity gap.

is this is how it can be applied?
create table mytable (IdNum numeric(12,0) identity)
with identity_gap = 10

removing the duplicate post

Yes.This is correct syntax.

Hi,
But the above syntax is not working.Shows syntax error at 'with' . Could u pls let me know the proper syntax and implementation code for identity_gap.

Thanks in Advance

Hi,
Here you go. It is working for me in ASE15 server.
1> create table mytable (Id numeric(12,0) identity) with identity_gap = 10
2> go
1> sp_help mytable
2> go
Name Owner Object_type Create_date
------- ----- ----------- -------------------
mytable dbo user table Jul 14 2011 1:06PM

(1 row affected)
Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity
----------- ------- ------ ---- ----- ----- ------------ --------- ---------------- ---------------------- ----------
Id numeric 6 12 0 0 NULL NULL NULL NULL 1
Object does not have any indexes.
No defined keys for this object.
name type partition_type partitions partition_keys
------- ---------- -------------- ---------- --------------
mytable base table roundrobin 1 NULL

partition_name partition_id pages row_count segment create_date
----------------- ------------ ----- --------- ------- -------------------
mytable_172576672 172576672 1 0 default Jul 14 2011 1:06PM

Partition_Conditions
--------------------
NULL

Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/Avg)
----------- ----------- ----------- -------------------- --------------------
1 1 1 1.000000 1.000000
Lock scheme Allpages
The attribute 'exp_row_size' is not applicable to tables with allpages lock scheme.
The attribute 'concurrency_opt_threshold' is not applicable to tables with allpages lock scheme.

exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ascinserts
------------ -------------- ---------- ----------------- ------------ -----------
1 0 0 0 10 0

(1 row affected)
concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
------------------------- --------------------- -------------------
0 0 0
(return status = 0)
1> select @@version
2> go

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Adaptive Server Enterprise/15.0.3/EBF 17769 ESD#4/P/Sun_svr4/OS 5.8/ase1503/2768/64-bit/FBO/Thu Aug 26 15:08:16 2010

(1 row affected)
1>

Ok.Iam using SQLAnyWhere10.Is there any equivalent concept in this version that supports identity_gap.

Hi,
Sorry, i don't know.