Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2004
    Posts
    30

    Unanswered: scope_identity()

    I have four tables:
    1- customer details
    2- bank details
    3- car details
    4- contract details

    All these tables are linked with the contract ID which is the primary key in table 4 and foriegn key in the rest. When a new customer inputs all the above data from the VB front, I want table 4 to give contract ID with a autonumber, which should be sent to the other tables, so that the contract in all tables are the same so that it is linked properly.....

    I think I do this using scope-Identity? if so hoe do I do this? I'm using enterprise manager.....

    Another question, customer table has a customer ID. What would be the primary key- customer ID, contract ID or both

    THANKS

  2. #2
    Join Date
    Jan 2004
    Posts
    30
    PLEASE HELP...I'm new to db design and really need this for my application to work.....

    THNAK YOU

  3. #3
    Join Date
    Feb 2004
    Posts
    492
    Quote Originally Posted by asbirpam
    (...)
    I think I do this using scope-Identity? if so hoe do I do this? I'm using enterprise manager.....
    I don't think you can use the EM for calling SCOPE_IDENTITY(). Besides, I think you'll need @@Identity instead. How is the data stored? All from VB? Do you use a stored procedure?

    Quote Originally Posted by asbirpam
    (...)
    Another question, customer table has a customer ID. What would be the primary key- customer ID, contract ID or both
    Which one is unique?

  4. #4
    Join Date
    Jan 2004
    Posts
    30
    Yeah all the data is inputted through VB. I previously tried to join the 4 tables into a view and after all the data was entered (and so the contract ID would be the same in each table)....however you can not update a view like that!!!

    Would it work if I joined the tables using a stored procedure...how would I use @@idenity.....what I want it to do is eg,

    in Contract table contract ID 4 = customer ID 1
    then in the customer table contract ID 4 should be respresently by customer 1 personal details

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    If a new row is inserted into a table with the identity column, @@identity holds the latest identity value inserted, fe:
    set nocount on

    create table tab1 (myint integer identity (10,1), myvar varchar(10))
    go

    insert into tab1 (myvar) values ('aa')
    select * from tab1
    select @@identity

    insert into tab1 (myvar) values ('aa')
    select @@identity

    go

    select * from tab1
    go

    drop table tab1
    go

    I think there might be an alternative for the hughe lump of a join trying the update all tables all at once (which I think won't work anyway): I'm thinking of updating/inserting each table seperately from the others and have a commit/rollback to have either the fresh data committed on success or rollbacked in case of failure. See BOL for commit examples.

  6. #6
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You need to actually change Kaiowas example back to SCOPE_IDENTITY(). People should really stop using @@identity. If you have a trigger on your table that inserts into another table with an identity column, you just captured the identity column of the table in the trigger instead of the one you meant to capture. This is the difference between @@identity and SCOPE_IDENTITY().

    Basically, in your stored procedure, insert into contract details, and SET @variable = SCOPE_IDENTITY(), which will be the IDENTITY you just inserted into contract details. Then populate the other tables with the values they need. If you need to, have seperate stored procedures. Return the @variable to your application as the output of the first insert. Use that in your future inserts.

    You should be doing everything through stored procedures btw.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Anyone want to mention that "prepopulating" tables is a bad idea?

    Are you collecting all the data at once?

    Is there a 1 to many relationship?

    Don't you have something more meaningful to identify a customer?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Also, think about this...when you want to look up a customer in the future, what are you going to use to identify them as the correct customer?

    Probably from a pick list right?

    Whatever you see on that list that makes you know it the right person...that's your key...

    Not some bs number...

    It's like...

    ok here's a list of arbitray numbers...pick the right one...no way

    Should add this to the list

    The Devils Spawn
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Posts
    492
    Quote Originally Posted by derrickleggett
    You need to actually change Kaiowas example back to SCOPE_IDENTITY(). People should really stop using @@identity. If you have a trigger on your table that inserts into another table with an identity column, you just captured the identity column of the table in the trigger instead of the one you meant to capture. This is the difference between @@identity and SCOPE_IDENTITY().
    And to think how often I actually used @@identity... lucky for me there's no triggering here, it's all in sp's!

  10. #10
    Join Date
    Jan 2004
    Posts
    30
    The customer has a customer ID which is used to differentate between each user. However, the four tables are linked through a contract ID, which is the pk (and autonumber) in contract table. When a new contract is created the data is stored over four tables... I want to be able to ensure the contract ID given in the contract table (autonumber) is copied to the other three pages.....

    How do I populate the other tables with the scope_identity i.e. the contract ID (autonumber) ??

  11. #11
    Join Date
    Feb 2004
    Posts
    492
    you could put it in a local variable, fe:

    declare @my_identity as integer

    insert into tab1 (myvar1) values ('example')
    set @my_identity = scope_identity()

    insert into tab2 (myint2, myvar2) values (@my_identity, 'scope_identity')


    You may want to add some error checking as well, change the set into a select allowing multiple variables to be filled in one statement. See @@error in BOL for this.

    Unless there's this thing called scope_error() or @@scope_error, er, oh no wait, maybe not.. ugh.. or perhaps you do. Oh brother!

    Well, I guess I shouldn't have started reading about that Surrogate Key-article Brett was so kind to point out, I think I'll be running for politics now.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I gotta ask....

    Are all the columns in the other tables nullable?

    If ContractId is a FK, and part of a composite PK, what the other non nullable PK compononent?

    Drumroll please.......
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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