Solved

Original @@identity value after insert trigger happens

Posted on 2008-10-09
3
693 Views
Last Modified: 2012-05-05
Hi,
I am trying to get the @@identity of a newly inserted row, after a insert happened.
The problem is that I also have an after insert tirgger on the table, that inserts to a log table, thereby updating @@identity. After the insert happens, I want the original ID of the insert, and not the id of the log table. Here is my trigger code:

alter TRIGGER MyTrigger
   ON  pmev
   AFTER INSERT
AS
BEGIN
      SET NOCOUNT ON;

      declare @PK1 int
      select @PK1 = PMEV_ID from inserted       
      insert into pmev2(PMEV_Descr1,PMEV_Descr2,PMEV_Descr3) values(getdate(),@PK1,3)

END
GO
0
Comment
Question by:wilcosw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22676600
use SCOPE_IDENTITY() instead of @@IDENTITY
0
 
LVL 1

Author Comment

by:wilcosw
ID: 22676634
Cool thanks,
here is also a link which explains it in more detail:
http://msdn.microsoft.com/en-us/library/aa259185.aspx
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22676643
correct
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question