April 15, 2009 at 2:25 pm
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?
April 15, 2009 at 2:36 pm
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
April 15, 2009 at 2:44 pm
[font="Verdana"]Have you looked at using the OUTPUT clause on your INSERT statement instead? Probably a better approach.
[/font]
April 15, 2009 at 2:46 pm
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.
April 15, 2009 at 2:48 pm
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?
April 15, 2009 at 2:53 pm
[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]
April 15, 2009 at 2:56 pm
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 INTSELECT @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
April 15, 2009 at 2:58 pm
April 15, 2009 at 2:58 pm
[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]
April 15, 2009 at 2:59 pm
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 INTSELECT @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
April 16, 2009 at 7:13 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 2, 2011 at 7:06 am
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