Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2011
    Posts
    15

    Red face Unanswered: Identity column and triggers

    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.

  2. #2
    Join Date
    May 2011
    Posts
    28

    Identity column and triggers

    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.

  3. #3
    Join Date
    Jul 2011
    Posts
    15

    identity gap

    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

  4. #4
    Join Date
    Jul 2011
    Posts
    15

    identity gap

    removing the duplicate post

  5. #5
    Join Date
    May 2011
    Posts
    28
    Yes.This is correct syntax.

  6. #6
    Join Date
    Jul 2011
    Posts
    15
    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

  7. #7
    Join Date
    May 2011
    Posts
    28
    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>

  8. #8
    Join Date
    Jul 2011
    Posts
    15
    Ok.Iam using SQLAnyWhere10.Is there any equivalent concept in this version that supports identity_gap.

  9. #9
    Join Date
    May 2011
    Posts
    28
    Hi,
    Sorry, i don't know.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •