sql 2005 trigger insert and dbmail

[janetb]

I have a stored proc that inserts records into a table, gets the identity, and uses that identity on a .net webpage. The page retrieves that identity key and processes some stuff on the page.

Everything was working fine until I tried to enable sql mail with triggers. I wanted to double-check some stuff, so I requested an sql email be sent whenever an insert occurs on the table above. Here's what I think is happening --- please correct and/or help me out with a work around?

I use the stored proc to insert a record.
I select the @@identity.
The trigger fires but uses select to retrieve the latest insert - thereby replacing the @@identity number returned to the page?

If this is true, could I do something like the following in the original stored proc? Is this a good idea or bad idea?

BEGIN 
  Declare @myID as int, @myBody1 as varChar(200) 
  Set @myID=0
  INSERT INTO table (fields) VALUES (@PID, more stuff);
  Set @myID = SELECT @@IDENTITY As [Identity];
  If @myID<>0 
    Begin
      Set @body1='<br />pid=' + more stuff.....
      Exec msdb.dbo.sp_send_dbmail 
                         @profile_name='profileName',
		@recipients='email@email.com',
		@subject='Temp History Insert',
		@body=@body1, 
		@body_format= 'HTML' ;
    End
END

[Island1]

With a code sample, etc.

http://blog.netnerds.net/2008/02/cre...e-mail-alerts/

Let us know if it helps, or if you have further issues.

Thanks.

Bill

[Island1]

I see you are being assisted on a crosspost at DBForums.

http://www.dbforums.com/showthread.php?p=6335045

My bad ...

Bill