scope_identity() strangeness

  • Many of the tables in our database use triggers, so I thought I'd use SCOPE_IDENTITY() to return the proper identity value. Imagine my surprise when I create a simple insert procedure, retrieve the identity value using SCOPE_IDENTITY() and return it as an output parameter - it ends up being null. The table in question uses an instead-of trigger; it only modifies 2 fields from what is in the inserted table: 1) substitutes a value for a column if the inserted value is null and 2) puts getdate() into a dtLastModified field. Am I misunderstanding how SCOPE_IDENTITY() works?

  • I'm not sure if I understood right...

    If you try to use the SCOPE_IDENTITY() to get the id of your currently inserted record this is not correct. The SCOPE_IDENTITY() would return the last identity inserted in any table from your trigger (maybe an audit table).

    Use the INSERTED pseudo table:

    DECLARE @id INT

    SELECT @id = YourIdColumn FROM INSERTED

    But

    You should never use row based solutions in triggers. If you make a bulk insert into your table the trigger is called only once and the INSERTED table contains more than one row.

    Greets

    Flo

  • [font="Verdana"]Have you looked at using the OUTPUT clause on your INSERT statement instead? Probably a better approach.

    [/font]

  • Sharon Levine (4/15/2009)


    Many of the tables in our database use triggers, so I thought I'd use SCOPE_IDENTITY() to return the proper identity value. Imagine my surprise when I create a simple insert procedure, retrieve the identity value using SCOPE_IDENTITY() and return it as an output parameter - it ends up being null. The table in question uses an instead-of trigger; it only modifies 2 fields from what is in the inserted table: 1) substitutes a value for a column if the inserted value is null and 2) puts getdate() into a dtLastModified field. Am I misunderstanding how SCOPE_IDENTITY() works?

    Since it is an INSTEAD OF trigger, I believe SCOPE_IDENTITY() will return NULL because you are saying instead of inserting into the table of my current scope Im going to do some other logic (doesnt necessarily have to be an insert at all), which in this case the insert being done falls out of your scope where you are calling scope_identity(). I would think you should use defaults on the columns being modified instead of doing this with a trigger.

  • I have a table tOrder which has the instead of trigger. I have a procedure pInsertOrder which has output parameter @intOrderID. The procedure takes some values & does a simple insert into the tOrder table. Right after the insert, I set @intOrderID = SCOPE_IDENTITY(). Why does @@IDENTITY return the id but SCOPE_IDENTITY() returns null?

  • [font="Verdana"]I suspect that scope_identity() is being checked inside the stored procedure doing the insert, not inside the trigger.

    Some code examples would help!

    [/font]

  • Florian Reischl (4/15/2009)


    I'm not sure if I understood right...

    If you try to use the SCOPE_IDENTITY() to get the id of your currently inserted record this is not correct. The SCOPE_IDENTITY() would return the last identity inserted in any table from your trigger (maybe an audit table).

    Use the INSERTED pseudo table:

    DECLARE @id INT

    SELECT @id = YourIdColumn FROM INSERTED

    But

    You should never use row based solutions in triggers. If you make a bulk insert into your table the trigger is called only once and the INSERTED table contains more than one row.

    Greets

    Flo

    You are not, in this case, SCOPE_Identity will not return anything, because the trigger DID NOT insert anything. The SCOPE_IDENTITY gets you the last Identity value that was INSERTED in the current session (Connection, or whatever word people will correct me on, ;))

    So, if you want to be sure it inserts data, either remove the Instead Of trigger, or select the ID of the last "Updated" row?

    Cheers,

    J-F

  • [font="Verdana"]Have you tried something like the following:

    declare @t table(OrderID int not null);

    insert into tOrder(...)

    output OrderID into @t(OrderID)

    values (...);

    select top 1

    @intOrderID = OrderID

    from @t;

    [/font]

  • J-F Bergeron (4/15/2009)


    Florian Reischl (4/15/2009)


    I'm not sure if I understood right...

    If you try to use the SCOPE_IDENTITY() to get the id of your currently inserted record this is not correct. The SCOPE_IDENTITY() would return the last identity inserted in any table from your trigger (maybe an audit table).

    Use the INSERTED pseudo table:

    DECLARE @id INT

    SELECT @id = YourIdColumn FROM INSERTED

    But

    You should never use row based solutions in triggers. If you make a bulk insert into your table the trigger is called only once and the INSERTED table contains more than one row.

    Greets

    Flo

    You are not, in this case, SCOPE_Identity will not return anything, because the trigger DID NOT insert anything. The SCOPE_IDENTITY gets you the last Identity value that was INSERTED in the current session (Connection, or whatever word people will correct me on, ;))

    So, if you want to be sure it inserts data, either remove the Instead Of trigger, or select the ID of the last "Updated" row?

    Hi J-F

    Thanks for correction! I wondered a bit while typing but I'm no trigger pro ;-).

    Greets

    Flo

  • I'd be interested in knowing why you are using an INSTEAD OF trigger. I have not seen many instances where an INSTEAD OF trigger was necessary. You might be better off with an AFTER trigger.

    As Grant and Bruce have mentioned using the OUTPUT clause would be the best way to return the identity value. Also check out this blog post.

  • Don't know if it might help, but the INSERTED pseudo table (inside the trigger) already contains the "to-be-commited" identity values (if the table has an identity column). For example:

    SELECT max(MyIdentityColumnName) FROM Inserted

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply