Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
janetb
Yak Posting Veteran
71 Posts |
Posted - 2008-04-23 : 16:15:30
|
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?[CODE]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' ; EndEND[/CODE] |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-23 : 16:19:24
|
Your select is not replacing the @@identity number returned to the page. You can not change the identity via a select.You haven't described what is happening once the trigger was put in place. What problem are you seeing?You should be using SCOPE_IDENTITY() instead of @@IDENTITY though. See SQL Server Books Online for why.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2008-04-23 : 16:29:04
|
Tara,Ummm. I said it badly, sorry. Does the trigger select replace the value returned to the .net page (not the sql identity)?I'm on the .net page and do something like:strSQL = "my sql statement to insert records with stored proc"Try ''''''Insert into historyChargesTemp table and get keyID cmd = New Data.SqlClient.SqlCommand(strSQL, cn) cmd.Connection.Open() varOrderNbr = cmd.ExecuteScalar....rest of page stuffWhat happens is that if I remove the trigger, the rest of the page (based on the varOrderNbr .net page variable) behaves fine. But, if I enable the trigger, the page doesn't process. I think what's happening is that the varOrderNbr on the page is being changed by the trigger? |
 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-23 : 16:35:27
|
Your trigger would only replace the value returned to the page if the trigger was also inserting into a table with an identity. This is the very reason why @@IDENTITY should be used and instead SCOPE_IDENTITY() should be. But you haven't posted your trigger code, so it's hard for us to know what's going on.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2008-04-23 : 16:47:00
|
Tara,Gotcha about the Scope_identity - will do but I'm posting the code as is so you'll see it, okay?declare @pid varChar(20), @wid as varchar(20), @styleID as varChar(20), @type as varChar(20)declare @start_date as varChar(20), @title as varChar(500), @fee as varChar(20), @nbr as varChar(20)declare @updated as varChar(20), @body1 as varChar(2000) select @pid=pid, @wid=wid, @styleID=styleID, @type=paymentType, @start_date=start_date, @title=rtrim(ltrim([title])), @fee=feeAmt, @nbr=orderID, @updated=[updated] from insertedset @body1='<br />pid=' + @pid + '<br />wid=' + @wid + '<br />start=' + @start_date + '<br />title=' + @title + '<br />style=' + @styleID + '<br />paymentType=' + @type + '<br />fee=' + @fee + '<br />date=' + @updated + '<br />orderNbr=' + @nbr Exec msdb.dbo.sp_send_dbmail @profile_name='myProfile', @recipients='email@email.net', @subject='Temp History Insert', @body=@body1, @body_format= 'HTML' ; |
 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-23 : 16:54:29
|
There is nothing in your trigger that would cause the identity value to be changed. You need to understand that triggers do not fire once per row but rather fires once for the entire changed record set. See my blog for details: http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx.I still don't understand what problem you are experiencing though. This part didn't include enough information:quote: But, if I enable the trigger, the page doesn't process.
Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2008-04-23 : 17:33:57
|
Tara,Sorry if I'm not clear. I'll try to explain.You haven't described what is happening once the trigger was put in place. What problem are you seeing?When I fire the stored proc and return the row inserted keyfield, I then use that returned variable to look up data in other tables and display some stuff on the webpage. When the trigger is not in place, that lookup happens fine. When the trigger is in place, the returned value is 17 instead of something like 13446. That's why I suspect it's returning something from the trigger and NOT the intended value. I remove the trigger, and everything is fine again.This is my trigger: declare @pid varChar(20), @wid as varchar(20), @styleID as varChar(20), @type as varChar(20) declare @start_date as varChar(20), @title as varChar(500), @fee as varChar(20), @nbr as varChar(20) declare @updated as varChar(20), @body1 as varChar(2000) select @pid=pid, @wid=wid, @styleID=styleID, @type=paymentType, @start_date=start_date, @title=rtrim(ltrim([title])), @fee=feeAmt, @nbr=orderID, @updated=[updated] from inserted set @body1='<br />pid=' + @pid + '<br />wid=' + @wid + '<br />start=' + @start_date + '<br />title=' + @title + '<br />style=' + @styleID + '<br />paymentType=' + @type + '<br />fee=' + @fee + '<br />date=' + @updated + '<br />orderNbr=' + @nbr Exec msdb.dbo.sp_send_dbmail @profile_name='myProfile', @recipients='email@email.net', @subject='Temp History Insert', @body=@body1, @body_format= 'HTML' ; |
 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-23 : 17:39:48
|
I don't see anything wrong in your trigger. Post the stored procedure and the actual relevant application code rather than pseudo code.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
|
|
|
|