trigger insert and dbmail - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

trigger insert and dbmail

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=' 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[/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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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 stuff

What 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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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 inserted

set @body1=' pid=' + @pid + ' wid=' + @wid + ' start=' + @start_date +
' title=' + @title + ' style=' + @styleID + ' paymentType=' + @type +
' fee=' + @fee + ' date=' + @updated + ' 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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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=' pid=' + @pid + ' wid=' + @wid + ' start=' + @start_date +
' title=' + @title + ' style=' + @styleID + ' paymentType=' + @type +
' fee=' + @fee + ' date=' + @updated + ' 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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources