Results 1 to 2 of 2

Thread: Need insert Cannot use Identity

  1. #1
    Steven McCall Guest

    Need insert Cannot use Identity

    Let me start off by saying I have posted this on:
    comp.databases.ms-sqlserver

    My apologies, try not to do that again.

    I have a table that I am trying to insert into with data from another
    table. Here is that Query:

    INSERT INTO item (identifier, name, customlgtxt1, weight,mainprice,
    customnumber3, customnumber4)
    Select partno, name, description, weight,price, qtyprice, qty
    From Import2

    This seems to work fine.

    My dilemma has to do with an 'id' column in that item table. This is
    incrementally updated by one..ie Natural Key. I have set it to
    Identity and used Set @@IDENTITY AS 'id'...worked like a charm. But
    for reasons that have to do with a front end admin tool used by the
    home office I can't set this column to have an Indentity property.
    Screws up the insert done with the admin tool.

    This is a item/price database by the way. Name of item, price,
    description, qty price..etc.

    So I've tried to put this trigger on the item table
    CREATE TRIGGER auto_fill ON [dbo].[item]
    FOR INSERT, UPDATE
    AS
    BEGIN
    declare @maxc int
    set @maxc = (select Max(id) from item)
    set @maxc = @maxc +1
    Select 'id' AS '@maxc',
    notorderable = '0',
    createdon = getdate(),
    createdat = getdate(),
    createdby = 'Steve',
    modifiedon = getdate(),
    modifiedat= getdate(),
    modifiedby = 'Steve'

    END

    But the Insert Into statement (see above) will not work...giving the
    error
    "Cannot insert the value NULL into column 'id', table
    'new_Catalog1v1.dbo.item"
    Which I know...that's why I set it to Identity..but that cannot be.

    So the question is how to set an autonumber (or natural key or I'm not
    sure of the name) that updated from the max(id)table when inserting from another table.

    And then...one more.

    I have to update a table named UNIQUEIDS with the lastest value of the
    id column (max(ID))..UNIQUEIDS keeps track of the latest value inserted
    into the id column for a number of tables. Here is another trigger I
    put on the item table to update the UNIQUEIDS table.

    create trigger upUniqueIds_item on [dbo].[item]
    for update,Insert, delete
    as
    begin
    Select @@Identity as 'id' from inserted
    Update uniqueids
    set id = @@identity
    Where tablename = 'item'
    End


    But of course this doesn't work if I can't set the columns to
    IDENTITY.

    I hope someone can help and I hope my explanation had made sense.
    Need to increment the id field using max(id) and update another table with the last imported value of max(id). One occurs during insert..another after the insert..i think?





  2. #2
    Guest

    Need insert Cannot use Identity (reply)

    Steven -

    Actually your trigger isn't really doing anything...it's just doing a SELECT statement which has no effect. Also, your trigger appears to be written from the standpoint that triggers run once for each record. Remember that triggers run for each SET of records that are updated/inserted so you need to think in sets when writing triggers.
    You probably are looking not for an INSERT trigger, but for an INSTEAD OF INSERT trigger which is new in SQL 2000.
    There is an article you should read at www.sqlmag.com InstantDoc 24144. Although the scenario is different, I believe what you are looking to do is a subset of what is described.

    Bill


    ------------
    Steven McCall at 5/6/2002 2:04:26 PM

    Let me start off by saying I have posted this on:
    comp.databases.ms-sqlserver

    My apologies, try not to do that again.

    I have a table that I am trying to insert into with data from another
    table. Here is that Query:

    INSERT INTO item (identifier, name, customlgtxt1, weight,mainprice,
    customnumber3, customnumber4)
    Select partno, name, description, weight,price, qtyprice, qty
    From Import2

    This seems to work fine.

    My dilemma has to do with an 'id' column in that item table. This is
    incrementally updated by one..ie Natural Key. I have set it to
    Identity and used Set @@IDENTITY AS 'id'...worked like a charm. But
    for reasons that have to do with a front end admin tool used by the
    home office I can't set this column to have an Indentity property.
    Screws up the insert done with the admin tool.

    This is a item/price database by the way. Name of item, price,
    description, qty price..etc.

    So I've tried to put this trigger on the item table
    CREATE TRIGGER auto_fill ON [dbo].[item]
    FOR INSERT, UPDATE
    AS
    BEGIN
    declare @maxc int
    set @maxc = (select Max(id) from item)
    set @maxc = @maxc +1
    Select 'id' AS '@maxc',
    notorderable = '0',
    createdon = getdate(),
    createdat = getdate(),
    createdby = 'Steve',
    modifiedon = getdate(),
    modifiedat= getdate(),
    modifiedby = 'Steve'

    END

    But the Insert Into statement (see above) will not work...giving the
    error
    "Cannot insert the value NULL into column 'id', table
    'new_Catalog1v1.dbo.item"
    Which I know...that's why I set it to Identity..but that cannot be.

    So the question is how to set an autonumber (or natural key or I'm not
    sure of the name) that updated from the max(id)table when inserting from another table.

    And then...one more.

    I have to update a table named UNIQUEIDS with the lastest value of the
    id column (max(ID))..UNIQUEIDS keeps track of the latest value inserted
    into the id column for a number of tables. Here is another trigger I
    put on the item table to update the UNIQUEIDS table.

    create trigger upUniqueIds_item on [dbo].[item]
    for update,Insert, delete
    as
    begin
    Select @@Identity as 'id' from inserted
    Update uniqueids
    set id = @@identity
    Where tablename = 'item'
    End


    But of course this doesn't work if I can't set the columns to
    IDENTITY.

    I hope someone can help and I hope my explanation had made sense.
    Need to increment the id field using max(id) and update another table with the last imported value of max(id). One occurs during insert..another after the insert..i think?





Posting Permissions

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