How do I get the ID of the last entry?

Hi,
I need to know the ID of the last entry so that I can use it in a trigger.
How do I get the ID of the last INSERT'ed record? Do I use Scope_Identity()
for that?
Thanks,
Sam> I need to know the ID of the last entry so that I can use it in a trigger.
What exactly are you doing in the trigger? Is it prepared for multiple-row
inserts (e.g. BULK INSERT or INSERT ... SELECT ...FROM)?
For a single insert within a stored procedure, you would use
SCOPE_IDENTITY(), but since you can't be sure that an insert is only one
row, the trigger can't say something like SCOPE_ALL_IDENTITIES(). If you
explain exactly what you are trying to do, we can help you with code that
uses the values from the inserted pseudo-table within the trigger (or moving
your logic outside of the trigger).
A|||Sam wrote:
> Hi,
> I need to know the ID of the last entry so that I can use it in a trigger.
> How do I get the ID of the last INSERT'ed record? Do I use Scope_Identity(
)
> for that?
> --
> Thanks,
> Sam
SCOPE_IDENTITY() will give you the IDENTITY value of the last inserted
row in a proc. In a trigger however you should use the INSERTED virtual
table instead. A well-behaved trigger needs to accommodate multiple row
updates so returning a single last inserted value is not sufficient.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The user is actually using an Access front-end to make enteries and that's
one record at a time. I also have a web interface to the app. That uses a
stored procedure to make entries into the table. That again will be one
record at a time.
Having said this I assume, SCOPE_IDENTITY() should do the job. Correct?
--
Thanks,
Sam
"Aaron Bertrand [SQL Server MVP]" wrote:

> What exactly are you doing in the trigger? Is it prepared for multiple-ro
w
> inserts (e.g. BULK INSERT or INSERT ... SELECT ...FROM)?
> For a single insert within a stored procedure, you would use
> SCOPE_IDENTITY(), but since you can't be sure that an insert is only one
> row, the trigger can't say something like SCOPE_ALL_IDENTITIES(). If you
> explain exactly what you are trying to do, we can help you with code that
> uses the values from the inserted pseudo-table within the trigger (or movi
ng
> your logic outside of the trigger).
> A
>
>|||Sam wrote:
> The user is actually using an Access front-end to make enteries and that's
> one record at a time. I also have a web interface to the app. That uses a
> stored procedure to make entries into the table. That again will be one
> record at a time.
> Having said this I assume, SCOPE_IDENTITY() should do the job. Correct?
> --
> Thanks,
>
SCOPE_IDENTITY() in a trigger will not return the value from the insert
that fired the trigger. Even if it did and even if what you said is
true, a trigger that can only support single row inserts is an accident
waiting to happen. Use the Inserted table and write set-based code so
that the trigger will be safe. Otherwise whoever, supports and
maintains the system will curse you one day.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
Thank you for setting me straight. I guess I resisted using the INSERTED
virtual table because I've never used it before. I'm looking at BOL but can
you suggest any other resource on the web that can get me started? Thanks
again.
Thanks,
Sam
"David Portas" wrote:

> Sam wrote:
> SCOPE_IDENTITY() in a trigger will not return the value from the insert
> that fired the trigger. Even if it did and even if what you said is
> true, a trigger that can only support single row inserts is an accident
> waiting to happen. Use the Inserted table and write set-based code so
> that the trigger will be safe. Otherwise whoever, supports and
> maintains the system will curse you one day.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||David Portas wrote:
> SCOPE_IDENTITY() in a trigger will not return the value from the insert
> that fired the trigger.
BTW, @.@.IDENTITY will. I say that for the sake of completeness not
because I think @.@.IDENTITY is the best solution.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
Just so I'm clear, should I use @.@.IDENTITY as opposed to INSERTED table? Is
that the best solution?
Thanks,
Sam
"David Portas" wrote:

> David Portas wrote:
> BTW, @.@.IDENTITY will. I say that for the sake of completeness not
> because I think @.@.IDENTITY is the best solution.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Well, like I said before,
"If you explain exactly what you are trying to do, we can help you with code
that
uses the values from the inserted pseudo-table within the trigger (or moving
your logic outside of the trigger)."
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:332CD2E2-C122-4786-8728-60B8562CFE23@.microsoft.com...
> David,
> Thank you for setting me straight. I guess I resisted using the INSERTED
> virtual table because I've never used it before. I'm looking at BOL but
> can
> you suggest any other resource on the web that can get me started? Thanks
> again.|||Well, like I said before,
"If you explain exactly what you are trying to do, we can help you with code
that
uses the values from the inserted pseudo-table within the trigger (or moving
your logic outside of the trigger)."
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:332CD2E2-C122-4786-8728-60B8562CFE23@.microsoft.com...
> David,
> Thank you for setting me straight. I guess I resisted using the INSERTED
> virtual table because I've never used it before. I'm looking at BOL but
> can
> you suggest any other resource on the web that can get me started? Thanks
> again.